simple formula

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a value in cell B1 and C1.

The value in B1 changes periodically (smaller or bigger).
I would like C1 to take on the value of B1 if B1 > C1
alternatively C1 should remain unchanged when B1 changes
if B1<C1. Is there a formula in cell C1 that will do
this for me?

Thanks for your help.
 
Hi Paul
I can't think of any way to achieve this without programming some code, so
here's a sample code
Place the code into your worksheet (Right-click on worksheet tab and choose
View code and paste)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
If Target.Value > [c1] Then
[c1] = [b1]
End If
End If
End Sub

HTH
Cordially
Pascal
 
You can do this with a circular reference or an event macro.

CRs:
Choose Tools/Options/Calculation and check the Iteration checkbox. Then
enter

C1: =MAX(B1:C1)



Event macro:

Put this in the worksheet's code module (right-click on the worksheet
tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
With Range("B1")
If IsNumeric(.Value) Then _
If .Value > Range("C1").Value Then _
Range("C1").Value = .Value
End With
End If
End Sub
 
Back
Top