I tried to tackle this a few months ago and had to drop it as I didnt have Excel Jeanie, now I have 
I need to look up a Currency in a table and return the value at the date the entry was made.
So this table is a record of all purchases:-
This is my currency table for 2014
I need to look up the currency code in the PO File, find it in the currency file then look at the date in the LPO file and ensure that the conversion is in that date period.
I convert Local to Euro then Euro to USD
I need to look up a Currency in a table and return the value at the date the entry was made.
So this table is a record of all purchases:-
| Excel Workbook | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | PO Date | Budget Yr | LPO No | Description | Supplier | Qty | Local Curr | Unit Cost Local Curency | Ext Cost Local Currency | Ext Cost Euro | Ext Cost USD | ||
| 2143 | Jan-14 | 2014 | 2439 | Wooden Cabinets & Shelves 3rd Floor | Japan Construction & Trading | 1 | BHD | 985.00 | 985.000 | 2,159.018 | 2,612.628 | ||
| 2144 | Jan-14 | 2014 | 2439 | Demolition of walls between Vault Room & Corridor | Japan Construction & Trading | 1 | BHD | 190.00 | 190.000 | 416.460 | 503.959 | ||
| 2145 | Jan-14 | 2014 | 2439 | Refixing of Ceiling & Flooring | Japan Construction & Trading | 1 | BHD | 85.00 | 85.000 | 186.311 | 225.455 | ||
| 2146 | Jan-14 | 2014 | 2440 | Daily Pre Paid Package for BCP Testing at BNPP DOHA | Progresssoft | 2 | USD | 1,500.00 | 3,000.000 | 2,479.134 | 3,000.000 | ||
| 2147 | Jan-14 | 2014 | 2441 | Miocrosoft Wireless 3000 Keyboard | HBC Printer Supplies Llc | 2 | AED | 240.00 | 480.000 | 107.992 | 130.682 | ||
| 2148 | Jan-14 | 2014 | 2442 | Maintenance of FileNet Scanners | Cad Gulf Llc | 1 | AED | 54,500.00 | 54,500.000 | 12,261.635 | 14,837.805 | ||
| 2149 | Jan-14 | 2014 | 2443 | Maintenance Contract for Etrali Bahrain - Jan 1 - Dec 31 | ETRALI | 1 | EUR | 23,170.00 | 23,170.000 | 23,170.000 | 28,038.017 | ||
| 2150 | Jan-14 | 2014 | 2443 | Maintenance Contract for Etrali Dubai - Jan 1 - Dec 31 | ETRALI | 1 | EUR | 10,377.00 | 10,377.000 | 10,377.000 | 12,557.208 | ||
| 2151 | Jan-14 | 2014 | 2444 | KVM Cables Cat5 KVM USB | Hewlett Packard (HP) | 5 | ZAR | 994.36 | 4,971.800 | 355.214 | 429.845 | ||
| 2152 | Jan-14 | 2014 | 2445 | Dry Contact for Touch Panel | Cotton House for Quilts | 5 | BHD | 125.00 | 625.000 | 1,369.936 | 1,657.759 | ||
| 2153 | Jan-14 | 2014 | 2446 | 5M LC-LC Multimode Cable | Peripherals And Parts Trading Llc | 30 | AED | 45.00 | 1,350.000 | 303.729 | 367.542 | ||
LPO Master Data | |||||||||||||
This is my currency table for 2014
| Excel Workbook | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 4 | Code | Description | Average | January | February | March | April | May | June | July | August | September | October | November | December | ||
| 6 | AED | DIRHAM DES E.A.U. | 4.851560877 | 4.95359147 | 5.07718791 | 5.05999369 | 5.09511625 | 5.00770004 | 5.02916505 | 4.91747935 | 4.82418388 | 4.63801186 | 4.60153441 | 4.57000882 | 4.44475781 | ||
| 23 | BHD | DINAR DE BAHREIN | 0.497996374 | 0.50846802 | 0.52115475 | 0.51937587 | 0.52300908 | 0.51402225 | 0.51628087 | 0.50474099 | 0.49517807 | 0.4760756 | 0.47232439 | 0.46910073 | 0.456225854 | ||
| 36 | CHF | FRANC SUISSE | 1.212498595 | 1.2245742 | 1.21365941 | 1.21786729 | 1.22038921 | 1.22031118 | 1.21412409 | 1.21717003 | 1.20612806 | 1.20584773 | 1.20594529 | 1.20139953 | 1.202567135 | ||
| 57 | EUR | EURO | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
| 62 | GBP | LIVRE STERLING | 0.802386448 | 0.82069617 | 0.82485978 | 0.82632039 | 0.82143599 | 0.81331468 | 0.80043258 | 0.79294007 | 0.79292442 | 0.77936185 | 0.7834162 | 0.7961097 | 0.776825553 | ||
| 93 | KWD | DINAR DU KOWEIT | 0.376307849 | 0.38139148 | 0.38905525 | 0.38789774 | 0.38989338 | 0.38425385 | 0.38576605 | 0.37894065 | 0.37387902 | 0.36404631 | 0.36336212 | 0.36272163 | 0.354486699 | ||
| 105 | MAD | DIRHAM DU MAROC | 11.15454532 | 11.1990551 | 11.2628429 | 11.2439727 | 11.2535228 | 11.2196928 | 11.2346965 | 11.1921012 | 11.149979 | 11.0665483 | 11.0376696 | 11.0134101 | 10.98105295 | ||
| 137 | QAR | RIYAL DU QUATAR | 4.809507424 | 4.911109 | 5.03371458 | 5.01639268 | 5.05058715 | 4.9627761 | 4.9850736 | 4.87457082 | 4.7823521 | 4.59848625 | 4.56213387 | 4.53013225 | 4.40676069 | ||
| 143 | SAR | RIYAL D'ARABIE SAOUDITE | 4.954612911 | 5.05804441 | 5.18410881 | 5.16648217 | 5.20262425 | 5.11349988 | 5.13555966 | 5.02110247 | 4.92584104 | 4.73739423 | 4.70019242 | 4.66954484 | 4.54096078 | ||
| 171 | USD | DOLLAR US | 1.320879167 | 1.34865 | 1.3823 | 1.3776 | 1.3872 | 1.3634 | 1.3693 | 1.3388 | 1.3134 | 1.2628 | 1.2528 | 1.2442 | 1.2101 | ||
| 191 | ZAR | RAND COMMERCIAL AFRIQUE DU SUD | 14.35131111 | 15.0634767 | 14.8500494 | 14.5006182 | 14.5947412 | 14.434044 | 14.5566862 | 14.3385483 | 13.9991368 | 14.2744391 | 13.84782525 | 13.75954607 | 13.9966221 | ||
Sheet2 | |||||||||||||||||
I need to look up the currency code in the PO File, find it in the currency file then look at the date in the LPO file and ensure that the conversion is in that date period.
I convert Local to Euro then Euro to USD
