Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Compare 2 values and return back the exchange rate based on the closest date

    Hi All,

    I'm trying to compare 2 values i.e. Currency = Currency and sale date = exchange rate date and trturn the exchange rate. In some instances there are no exchange rates for the sale date so I'm trying to take the closet one before that date.

    I seem to be close but having issues when I copy the formula down.

    Any help will be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    ..see my attached version.

    I took the liberty of moving the Rates data to a separate sheet [Rates]
    I added a button and associated routine to sort the rates data by currency and exchange-rate-date.
    (the routine also creates/updates associated named ranges for use in the formulas)

    I wanted to show whether the exchange rate returned by the array formula was based on matching the exact date, or by using the closest date for which a rate was available.
    To do this, I used a hidden 'helper column' C, which returns the row number where the exact match occurs (easier for checking the results), or returns #N/A if no matching date is found.
    I then checked for a #N/A result in a conditional-formatting formula, which will over-ride my default green-cell background to pink for the returned exchange rate.

    Hope this helps.

    zeddy
    PS. So, no data for GBP 's then????
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    ..I just read again you wanted "closet one before that date."
    ..my posted file returns the closest date, even if that closest date comes later than the sales-date.

    Do you need an updated file?

    zeddy

  4. #4
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy, Thanks for your help so far.

    Its a GBP company so no need for GBP - GBP exchange rates

    I really need this as a formula and not macro for a change, do you think its possible? Happy to have this on separate sheets as that's how I originally wanted it.

    Thanks

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Here's a concept you might want to try, sorry I don't have time to flesh it out as I'm off to my Tuesday Bowling for Lunch group.

    Setup a Rate sheet for each currency with just Date and Rate (use currency code as Sheet Name). You can now use a VLookup using an Indirect function to select the proper currency sheet. Two caveats, first make sure the rate sheets are sorted by date, secondly, use a defined name for the lookup range (same name on each sheet with a scope of worksheet).

    VLookup can be set via option to select exact match (false) or approximate match (true) as the last parameter, or just leave it off as it defaults to True. It will search the dates for a match and not finding one it will revert back to the previous entry when it finds one larger than the search value.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Well got back from Lunch...I mean Bowling and got to work on this. My original concept was a little off but the general Idea does work.

    Actually you just need to create Range Names for each currency that matches the currency code (abbreviation), you can also name the sheet the same way.

    ExtractSheet.PNG

    You can see the sheet I used for Advanced Filters to extract the currencies one at a time for placing on the sheets (see the tab names). I also pasted the Range Names List on this sheet for reference.

    Once this is setup two simple formulas can be used (as shown they can be dragged down the columns).

    To retrieve the appropriate Exchange Rate: =VLOOKUP($B2,INDIRECT($A2),2,TRUE)

    To retrieve the date that rate was effective: =VLOOKUP($B2,INDIRECT($A2),1,TRUE)

    Sample:
    Sample.PNG

    Issues:

    For some reason, and I'm going to research this, VLookup doesn't seem to like Dynamic Range Names. This means that we'll need code (VBA) to update the tables when they are changed. This is very straight forward code but if you don't want code in the workbook this becomes a problem, e.g. regenerating the Range Names manually when the tables are changed. Actually, if you insert the new data in the middle of the table the Range Name will adjust automatically you just have to remember to resort the table by date. Easy enough but you need to remember to do it!

    Each Currency MUST have an entry in the table that has a Date BEFORE any date for that currency in Sheet1 or it will not return valid values for dates in Sheet1 that are before the first date. Thus you'll notice the 12/31/2015 date I added to each currency table.

    Here's my test file: Exchange Rate RGV1.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok, here's the code to automatically update the range names for the currency sheets. Place the following code in the Workbook Module. (don't forget to save it as a .xlsm or xlsb file!)

    Code:
    Option Explicit
    
    Sub Workbook_SheetChange(ByVal Sh As Object, _
                             ByVal Source As Range)
       
       If (Sh.Name = "Sheet1" Or Sh.Name = "Ext") Then Exit Sub
       If (Intersect(Range("B:B"), Source) Is Nothing) Then Exit Sub
           
       On Error Resume Next
       ActiveWorkbook.Names(Sh.Name).Delete
       On Error GoTo 0
        
       Range("A2:B2").Select
       Range(Selection, Selection.End(xlDown)).Select
    
       With ActiveWorkbook
           .Names.Add Name:=Sh.Name, RefersTo:="=" & Selection.Address()
           With .ActiveSheet.Sort
               .SortFields.Clear
               .SortFields.Add Key:=Range("A2"), _
                               SortOn:=xlSortOnValues, _
                               Order:=xlAscending, _
                               DataOption:=xlSortNormal
               .SetRange Range(Selection.Address())
               .Header = xlNo
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
          End With   '.Worksheets(Sh.Name).Sort
           
          Range("A2").Select
           
       End With      'ActiveWorkbook
        
    End Sub      'Workbook_SheetChange
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi RG,

    Thanks for your help, makes me feel better as I was trying to have it on it on 2 sheets only with no macros, I.e exchange rates on one and then the sale sheet on the other.

    Once this is created as a template someone who ust about knows how to switch a computer on will be using it so even them copying and pasting is bad enough let along splitting each sheet per currency etc.

    thanks again for your help.

  9. #9
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi All,

    I've been away for a few weeks and thought I'd check to see if anyone has managed to crack it by having 1 sheet with 2 sheets with 1 formulae?

    Thanks in advance

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG, I think the range is OK. GBP doesn't exist, however. Is that the issue?

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is it OK to sort the exchange rate table by currency code and date? If so, the formula is pretty simple.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Yep, fine to sort. Thanks

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In that case, sorting the table on currency and then date in ascending order, the formula is:

    =IF(A2="GBP",1,LOOKUP(1,1/(RATE_CURR=A2)/(EXC_DATE<=B2),EXCHANGE_RATES))

    Note that you don't have any 1/1/2016 data which is why you will get errors for any non-GBP currency for the first few rows.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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