Help on Echange rare

[Hoshyar]

Hi,

I have an echange rate problem, Hope someboy will help.

Column "B4:B200" where I write a cost in a certain currency, say Euro. In
column "A4:A2000" I return the USD value using a formual that refers to cells
A1 and B1. Column C4:C2000 is the date.

A1= 1 EUR
B1= 1.3 USD.

and the simple formula which is typed in cells "A4:A2000" is (=A1/B1).

This works as long as the value of USD is 1.3. Suppose that the value of
USD changed today from 1.3 to 1.1 against Euro. when I type the new exhange
rate in cell B1, the old valuses are changing as well. is there a way that
the value change only effect today onward and keep the previous cells with
the old value? I think it should be a relation between dates in culumn C and
the exchange rates in Cells A1 and B1, but I dont know how it do it.

Your help is appreciated
Hoshyar

 

[Tom Ogilvy]

one fairly simple way would be before you change the exchange rate, you
select the existing data in the columns and do Edit=>Copy, then immediately
Edit=>PasteSpecial and select values. This will replace the formulas in the
cells with the values they display.

Another alternative is to put the exchange rate at the top of each column
and have the formulas refer to the exchange rate at the top of the column.
then, not only do you have the previously calculated values, you also have
the previous exchange rate.

 

[Hoshyar]

Thanks Tom

Is there a way to change the formula in clumn A to value every time you type
the cost in the cell nex it to it in colum B?

I mean first to calulate the exchange rate and immediately change it to a
value in stead of keeping it as formula. this way will work if this function
is available.

Thanks once again
Hoshyar

 

[Tom Ogilvy]

right click on the sheet tab and select View code. Put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
On Error GoTo ErrHandler
If Target.offset(0,-1).HasFormula Then
Application.EnableEvents = False
Target.offset(0,-1).Formula = Target.Offset(0,-1).Value
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub

 

[Hoshyar]

Many thanks Tom

compile error message
ambigious name detecte: worksheet_change.


I have also other modules in this worksheet, maybe interfacing.
Pleae advise.
Best regards
hoshyar

 

[Hoshyar]

Or would you mined if I send you my workbook?
I would appreciate it very much
HOshyar

 

[Tom Ogilvy]

That just means you have to Change events in the worksheet. You can only
have one.