+ Reply to Thread
Results 1 to 7 of 7

Historic Currency Exchange Rate Lookup

  1. #1
    Registered User andy_dyer's Avatar
    Join Date
    03-12-2004
    Location
    Isle of Wight, UK
    MS-Off Ver
    Office 2010
    Posts
    7

    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
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,079

    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
    Attached Files Attached Files
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,079

    Re: Historic Currency Exchange Rate Lookup

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

  4. #4
    Registered User andy_dyer's Avatar
    Join Date
    03-12-2004
    Location
    Isle of Wight, UK
    MS-Off Ver
    Office 2010
    Posts
    7

    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. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,079

    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. #6
    Registered User andy_dyer's Avatar
    Join Date
    03-12-2004
    Location
    Isle of Wight, UK
    MS-Off Ver
    Office 2010
    Posts
    7

    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!!

    Thanks again for your time

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,079

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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