Hi

I have a formula in a cell currently, that is based on the value i
another cell. As the value in the other cell increases I want th
formula to be in effect and continue to calculate increasing values

However, if the value in the other cell goes down, I no longer wan
this formula to do anything, ie. the value it last calculated remain
in effect

For your information this is to create a rising stop loss in an exce
portfolio. Hence the price may go up or down, but the stop los
limit must only continue to rise

Thanks

This could do with event disabling to stop unnecessary re-entry, error
handling, and testing for the target range

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Address = "$C$2" Then
If Target.Value Range("C1").Value Then
Range("c1").Value = Target.Value
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don Guillett" wrote in message
...
right click on the sheet tabview codecopy/paste this.modify ranges to
suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c2") < Range("c1") Then Exit Sub
Range("c1").Value = Range("c2")
End Sub

--
Don Guillett
SalesAid Software

"icehot" wrote in message
...
Hi,

I have a formula in a cell currently, that is based on the value in
another cell. As the value in the other cell increases I want the
formula to be in effect and continue to calculate increasing values.

However, if the value in the other cell goes down, I no longer want
this formula to do anything, ie. the value it last calculated remains
in effect.

For your information this is to create a rising stop loss in an excel
portfolio. Hence the price may go up or down, but the stop loss
limit must only continue to rise.

Thanks,

right click on the sheet tabview codecopy/paste this.modify ranges to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c2") < Range("c1") Then Exit Sub
Range("c1").Value = Range("c2")
End Sub

--
Don Guillett
SalesAid Software

"icehot" wrote in message
...
Hi,

I have a formula in a cell currently, that is based on the value in
another cell. As the value in the other cell increases I want the
formula to be in effect and continue to calculate increasing values.

However, if the value in the other cell goes down, I no longer want
this formula to do anything, ie. the value it last calculated remains
in effect.

For your information this is to create a rising stop loss in an excel
portfolio. Hence the price may go up or down, but the stop loss
limit must only continue to rise.

Thanks,

if you want a formula, you need to create an intentional circular reference.

You would have to go to tools=Options, then under the calculate tab, select
iterate and set maximum iterations to 1.

then you can create an intentional circular reference

In B1 (refering to A1) for instance use a formula like:

=if(A1="",0,if(A1 B1,A1,B1))

the disadvantage with this is that if you improperly create a formula that
has a circular reference, you will not be warned.

Note that this is an application level setting and might have to be set each
time you open the workbook to avoid the warning (or whenever it gets
changed).

--
Regards,
Tom Ogilvy


"icehot" wrote in message
...
Hi,

I have a formula in a cell currently, that is based on the value in
another cell. As the value in the other cell increases I want the
formula to be in effect and continue to calculate increasing values.

However, if the value in the other cell goes down, I no longer want
this formula to do anything, ie. the value it last calculated remains
in effect.

For your information this is to create a rising stop loss in an excel
portfolio. Hence the price may go up or down, but the stop loss
limit must only continue to rise.

Thanks,

If c1 is the cell with changing values, and c2 holds the value, then you
can use c1's change event to increase the value in c2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then
If Target.Value Range("C2").Value Then
Range("C2").Value = Target.Value
End If
End Sub

Patrick Molloy
Microsoft Excel MVP
"icehot" wrote in message
...
Hi,

I have a formula in a cell currently, that is based on the value in
another cell. As the value in the other cell increases I want the
formula to be in effect and continue to calculate increasing values.

However, if the value in the other cell goes down, I no longer want
this formula to do anything, ie. the value it last calculated remains
in effect.

For your information this is to create a rising stop loss in an excel
portfolio. Hence the price may go up or down, but the stop loss
limit must only continue to rise.

Thanks,