For example, if the user types January instead of Jan, the sheet referencing trick won't work.

You might want to make sure that the user can only put in values that match your setup, perhaps using validation. Note that you will want to set this range to match the number of currencies you are converting from. In cell D2 to get the result you require. The following table shows the earliest available date for each currency in our. A nice example is the chart below, which shows the Japanese Yen to US Dollar exchange rate since October 5, 1993. Our currency exchange rates charts now allow you to plot up to 14 years of data, depending on the currency. =VLOOKUP(C2, INDIRECT(CONCATENATE(B2, '!A1:B5')),2, FALSE)*A2 Historical Currency Exchange Rate Charts. Select from the list US dollar Euro Australian dollar Brazilian real British pound Canadian dollar Chilean peso Chinese yuan renminbi Czech koruna Danish krone Euro Hong Kong dollar Hungarian forint Indian rupee Indonesian rupiah Israeli shekel Japanese yen. So, if the content of cell A1 = 'X256', you can use the formula =10*Indirect(A1) and the result will be whatever is in cell X256 multiplied by 10Īssuming the sheet your user fills in has looks a bit like this A | B | C | Dġ ConvertAmount | Month | ExchangeCurrency | ExchangeAmountĪnd the rest of the sheets are named Jan, Feb, Mar, Apr etc and have the Currency type in alpha order and Currency and Rate in columns A and B respectively of each sheet. Create exchange rate chart and retrieve historical exchange rate data. Indirect allows you to use a string as a reference to a worksheet location. I understand it, but do you happen to know any good source where to get monthly. I browsed through some APIs for this and it seems they are all a bit on a heavy side for my needs and for a commercial purpose not free. I need to import monthly historical exchange rates to my report. VLookup allows you to query a cell for a value (in your case the input Currency type), and look up a table to find that value and return the value in the same row in a different column in that table (in your case the exchange rate). Monthly historical currency exchange rates. You need to investigate VLookup and Indirect.

Historic currency rates