Hi All, I have a simple Spreadsheet that racks what we buy and converts from multiple currencies to a USD & EUR figure
1.00 QAR 38400.000 38400.000 €7,680.00 $10,971.43
So Qty 1 x QAR38400 = €7680 or $10971.43 depending upon Exchange Rates
My rates are in a table and fixed each month so at present I use the formula +total val * vlookup(Currecy, table, col no,0)* qty for Eur and Division for USD.
2013 2014
Month JAN JAN JAN JAN
Currency € $ € $
AED 0.2 3.5 0.2 3.500
BHD 2 0.4 2 0.350
USD 0.7 1.0 0.7 1.000
QAR 0.2 3.5 0.2 3.500
KWD 2.6 0.3 2.6 0.269
SAR 0.2 3.5 0.2 3.500
EUR 1 0.7 1 0.700
GBP 1.2 0.6 1.2 0.583
CHF 0.8 0.875 0.8 0.875
MAD 0.1 7.0 0.1 7.000
ZAR 0.1 7.0 0.1 7.000
Now in my main table I have a date field and what I need to do is for the lookup to look at the date field and use the Month to lookup in the exchange table and bring that result, bearing in mind that each month I will add new column to represent that month. Only 2013 is fixed.
1.00 QAR 38400.000 38400.000 €7,680.00 $10,971.43
So Qty 1 x QAR38400 = €7680 or $10971.43 depending upon Exchange Rates
My rates are in a table and fixed each month so at present I use the formula +total val * vlookup(Currecy, table, col no,0)* qty for Eur and Division for USD.
2013 2014
Month JAN JAN JAN JAN
Currency € $ € $
AED 0.2 3.5 0.2 3.500
BHD 2 0.4 2 0.350
USD 0.7 1.0 0.7 1.000
QAR 0.2 3.5 0.2 3.500
KWD 2.6 0.3 2.6 0.269
SAR 0.2 3.5 0.2 3.500
EUR 1 0.7 1 0.700
GBP 1.2 0.6 1.2 0.583
CHF 0.8 0.875 0.8 0.875
MAD 0.1 7.0 0.1 7.000
ZAR 0.1 7.0 0.1 7.000
Now in my main table I have a date field and what I need to do is for the lookup to look at the date field and use the Month to lookup in the exchange table and bring that result, bearing in mind that each month I will add new column to represent that month. Only 2013 is fixed.