Home

Update Daily Exchange Rate to a spreadsheet everyday to a new date

%3CLINGO-SUB%20id%3D%22lingo-sub-506581%22%20slang%3D%22en-US%22%3EUpdate%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506581%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20updates%20the%20daily%20exchange%20rate%20to%20a%20given%20cell.%20I%20have%20a%20column%20of%20dates%20that%20I%20wish%20to%20have%20that%20given%20date%20equal%20that%20day's%20rate%20without%20changing%20the%20previous%20date's%20rates.%20I%20have%20tried%20IF%20AND%20formulas%20that%20return%20the%20correct%20info%20to%20that%20day's%20date%20but%20change%20all%20previous%20dates%20to%20%22FALSE%22.%20Other%20VLOOKUP%20formulas%20return%20%23NAME%3F%20or%20%23VALUE!.%20Any%20help%20would%20be%20appreciated.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-506581%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506598%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506598%22%20slang%3D%22en-US%22%3ECould%20you%20post%20a%20quick%20example%20file%20of%20what%20you%20are%20trying%20to%20do.%3CBR%20%2F%3EIt's%20not%20too%20clear%20from%20the%20description%20sorry%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506758%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506758%22%20slang%3D%22en-US%22%3E%3CP%3ETHank%20you%20for%20your%20interest%20in%20helping%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E.%20I%20have%20attached%20the%20file.%20It%20is%20macro%20enabled%20MSO2016.%20I%20use%20this%20worksheet%20in%20other%20work%20books%20to%20update%20the%20current%20exchange%20rates%20for%20the%20date%20of%20a%20given%20transaction%20as%20the%20rates%20do%20change%20daily.%20The%20Exchange%20rate%20at%20the%20top%20will%20update%20automatically%20or%20when%20refresh%20external%20reference%20is%20selected.%20I%20have%20been%20updating%20the%20right%20column%20manual.%20I%20know%20this%20can%20be%20done%20automatically%20without%20changing%2Fclearing%20the%20previous%20date's%20rates.%20Hope%20this%20helps%20to%20explain%20what%20I%20am%20trying%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-507596%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-507596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330921%22%20target%3D%22_blank%22%3E%40Dallen8028%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20trying%20to%20keep%20a%20history%20of%20the%20rates%20on%20particular%20dates%20then%20you%20will%20need%20to%20use%20a%20macro%20to%20run%20a%20copy%20paste%20values%20routine.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20fixed%20up%20a%20couple%20of%20the%20formula%20but%20I%20don't%20think%20you%20can%20do%20what%20you%20want%20to%20do%20using%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510234%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510234%22%20slang%3D%22en-US%22%3EThank%20you%20for%20fixing%20the%20other%20formulas.%20Do%20you%20know%20VBA%20enough%20to%20generate%20a%20Macro%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511475%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Daily%20Exchange%20Rate%20to%20a%20spreadsheet%20everyday%20to%20a%20new%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511475%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330921%22%20target%3D%22_blank%22%3E%40Dallen8028%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20not%20really%20the%20sort%20of%20thing%20that%20I%20could%20help%20with%20quickly%20here%20sorry.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dallen8028
New Contributor

I have a spreadsheet that updates the daily exchange rate to a given cell. I have a column of dates that I wish to have that given date equal that day's rate without changing the previous date's rates. I have tried IF AND formulas that return the correct info to that day's date but change all previous dates to "FALSE". Other VLOOKUP formulas return #NAME? or #VALUE!. Any help would be appreciated. Thank you

5 Replies
Could you post a quick example file of what you are trying to do.
It's not too clear from the description sorry

THank you for your interest in helping @Wyn Hopkins. I have attached the file. It is macro enabled MSO2016. I use this worksheet in other work books to update the current exchange rates for the date of a given transaction as the rates do change daily. The Exchange rate at the top will update automatically or when refresh external reference is selected. I have been updating the right column manual. I know this can be done automatically without changing/clearing the previous date's rates. Hope this helps to explain what I am trying to achieve.

@Dallen8028 

 

If you are trying to keep a history of the rates on particular dates then you will need to use a macro to run a copy paste values routine.

 

I've fixed up a couple of the formula but I don't think you can do what you want to do using formula

 

 

Thank you for fixing the other formulas. Do you know VBA enough to generate a Macro?

Hi @Dallen8028 

 

It's not really the sort of thing that I could help with quickly here sorry.

 

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies