Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Applying a currency rate that doesn't change when source updated
I have a sheet with three columns that on entering a value in USD
automatically calculates the GBP and EUR values based on a reference list of rates on another sheet. My problem is that when I update the rates I don't want any historically entered information to be updated, only new entries. Is this possible? |
#2
|
|||
|
|||
Hi
Two possible ways to achieve this a) Rates list is historical, i.e when updated, new rates are inserted at end of rate table, and current date is added into date column for new entries. Probably you have to use VBA for this. b) When workbook is opened, for all entries with date less than current day, all formulas are replaced with values. Probably you have to write an Open event for this. Arvi Laanemets "Redsmartie" wrote in message ... I have a sheet with three columns that on entering a value in USD automatically calculates the GBP and EUR values based on a reference list of rates on another sheet. My problem is that when I update the rates I don't want any historically entered information to be updated, only new entries. Is this possible? |
#3
|
|||
|
|||
I think that both your suggestions may be beyond me however it has
occurred to me that there is a third option that while also a little beyond me may be achievable in principal. I'm wondering if I can write a macro that does the calculation and writes the values based on the current exchange rate table. Or am I on a hiding to nothing with this? On 2005-04-01 17:27:42 +0100, "Arvi Laanemets" said: Hi Two possible ways to achieve this a) Rates list is historical, i.e when updated, new rates are inserted at end of rate table, and current date is added into date column for new entries. Probably you have to use VBA for this. b) When workbook is opened, for all entries with date less than current day, all formulas are replaced with values. Probably you have to write an Open event for this. Arvi Laanemets "Redsmartie" wrote in message ... I have a sheet with three columns that on entering a value in USD automatically calculates the GBP and EUR values based on a reference list of rates on another sheet. My problem is that when I update the rates I don't want any historically entered information to be updated, only new entries. Is this possible? |
#4
|
|||
|
|||
Hi
before changing the rates, select the data you've already entered and copy it, then choose edit / paste special / values to change the formulas into the results ... then they will not recalculate when you change the underlying information. you could make a macro to do this if you wanted to. note, it is a good idea to try something like this on a copy of your workbook to ensure it works as you want. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Redsmartie" wrote in message ... I have a sheet with three columns that on entering a value in USD automatically calculates the GBP and EUR values based on a reference list of rates on another sheet. My problem is that when I update the rates I don't want any historically entered information to be updated, only new entries. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Currency symbol dependant upon selected cell value | Excel Discussion (Misc queries) | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
im trying to change the default currency for my excel from $ to M. | Excel Discussion (Misc queries) | |||
How do I link two cells as to allow me to change the value in eit. | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) |