# Historic Currency Exchange Rate Lookup

1. ## Historic Currency Exchange Rate Lookup

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

2. ## Re: Historic Currency Exchange Rate Lookup

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

3. ## Re: Historic Currency Exchange Rate Lookup

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

4. ## Re: Historic Currency Exchange Rate Lookup

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

5. ## Re: Historic Currency Exchange Rate Lookup

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

Should work.

Alan

6. ## Re: Historic Currency Exchange Rate Lookup

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

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

7. ## Re: Historic Currency Exchange Rate Lookup

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1