Help on Echange rare
 

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

Help on Echange rare
 

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

Help on Echange rare
 

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

Help on Echange rare
 

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

Help on Echange rare
 

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

"Tom Ogilvy" wrote:

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

Help on Echange rare
 

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

"Tom Ogilvy" wrote:

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

Help on Echange rare
 

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

--
Regards,
Tom Ogilvy

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

"Tom Ogilvy" wrote:

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