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

--
Regards,
Tom Ogilvy


"Hoshyar" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Hoshyar" wrote in message
...
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