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?

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?

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?

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?