Conditional cell update and circular references

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

This is driving me crazy! I want to set the value of
cell A2 to A1 when A1 > A2, i.e., I want to save the
largest value of A1 in A2. But I get a circular
reference error if I enter =IF(A1>A2,A1,A2)in cell A2.
Or I get a value of TRUE if I enter =IF(A1>A2,A1)in A2.
What I rally want is a function that will leave the
contents of A2 unchaged if A1 is not greater that A2.

Thanks,
Chuck
 
Chuck,

You cannot have a value and a formula in A2. So if, A1 is 7 and A2 is 8, it
works as you want, but if A1 is 10 and A2 is 7, as soon as you put the
formula in A2, it no longer contains 7. You could use another cell, say B1,
and in A2 put
=IF(A1>B1,A1,B1)

or else you could use Worksheet change code for A2

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Address = "$A$2" Then
If Range("A1").Value > Range("A2").Value Then
Range("A2").Value = Range("A1").Value
End If
End If

ws_exit:
Application.EnableEvents = False
End Sub

this goes in the worksheet code module.
 
I think I'd use three cells (a1, a2, a3) and just put:

=max(a2,a3)

in A1

But if you use the worksheet_change that Bob wrote, change the next to last line
from:

Application.EnableEvents = False
to
Application.EnableEvents = True

(I think a copy|paste error crept in--but it might have been automatic typing,
too!)
 
Back
Top