How do I create a cell whose value can only go up, not down?

I

icehot

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
 
B

Bob Phillips

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)
 
D

Don Guillett

right click on the sheet tab>view code>copy/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
 
T

Tom Ogilvy

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).
 
P

Patrick Molloy

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
 
Top