Historic Currency Exchange Rate Lookup

**andy_dyer** · 11-30-2010, 01:04 PM

Hi,

I have got given a load of data... historic exchange rates from Oct 09 - Oct 10.

The current front sheet to these individual sheets has the ability to change the base currency and all the indivdual currency rates then change.

I've added these seperate data sheets into one workbook with seperate worksheets entitled Oct 2009, Nov 2009 etc

I now wish to have these cells for each currency check the date first and then lookup the right exchange rate for the currency in question...

The date field is in cell I2 and the base currency code is in E3...

I have just been upgraded to Excel 2010 which isn't helping as I'm not used to it yet...

How can I get these currency fields to check the worksheets for the right date (in cell L1 on each sheet) to match I2 and then match the base currency in order to populate the exchange rate correctly...

Hope that makes sense - any help gratefully received

**alansidman** · 11-30-2010, 04:21 PM

Ok, to make this work, you need to make several changes. Your validation lookup in column S must be changed to Text format and change the dates to Oct 2009, Nov 2009, etc. I only changed the formula in the cell for the US to EUR and used this formula
=HLOOKUP($E$3,INDIRECT("'"&$I$2&"'"&"!D5:AX52"),46,FALSE)

The indirect changes the value in I2 to a text reference that matches the names of the worksheets. You will have to do this for each of your formulae conversions.

Good Luck.
Alan

**alansidman** · 11-30-2010, 04:30 PM

I think I uploaded the wrong version. Check this one first.

**andy_dyer** · 11-30-2010, 06:12 PM

Thanks Alan - will that formula really check all of the worksheets for the month? Can't see where it is doing that?

I'm away from my pc and on my phone but will give your suggestion a go tomorrow! Thanks ever so much for your help!

Andy

**alansidman** · 11-30-2010, 06:28 PM

You will have to put the formula in each cell that you wish to lookup and change the row reference accordingly.

Should work.

Alan

**andy_dyer** · 11-30-2010, 11:53 PM

Thanks Alan - not entirely sure how and why that works but it does :-)

You've just saved me hours of manual work!!

Thanks again for your time

**alansidman** · 12-01-2010, 12:17 AM

Glad to be of help. It is a simple vlookup using the date in your validation to coincide with the sheet name to determine which historical table to use. That was the reason to change that to text vs a date format you had previously. The indirect statement does that. As long as all your tables are arranged exactly the same, this will work for you.

Alan