Circular Reference

[miker1999]

I am working with a worksheet and have hit a circular reference. What
would be a correct formula for the one I am using:

=IF(A1="TRANSFER","Transfer",IF(A1="PENDING","New
Hire",IF(A1="OPEN","",K1)))

There are a few other values that A1 can be, however for those other
values, I would like K1 to stay at whatever value it currently is
(Transfer or New Hire).

I know where my circular ref is...just need a better formula I guess.
Thanks!
Mike

 

[Bob Phillips]

Mike,

If this formula is in K1, you have a problem. K1 can either have a value or
a formula, not both. As soon as you put the formula in K1, the value is
gone, and you cannot test it.

This can be managed by worksheet event code, this is the sort of thing

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "TRANSFER" Then
.Offset(0, 10).Value = "Transfer"
ElseIf .Value = "PENDING" Then
.Offset(0, 10).Value="New Hire"
End If
End With
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)