if my If is FALSE, do nothing an leave the current value at whatever it is...

[miker1999]

Hi all,
here is the troubling formula:

=IF(A3="1-PENDING","New Hire",IF(A3="2-TRANSFER","Transfer",DO NOTHIN
AND LEAVE THE VALUE AT WHATEVER IT IS))

In A3, there is another option (3-COMPLETE). If the user selects this
I would like the value to stay at whatever it is (so we know if th
person was a New Hire or Transfer).

Help..is this possible

 

[Aladin Akyurek]

=CHOOSE(--LEFT(A3),"New Hire","Transfer","")

which exploits the leading digit in 1-PENDING, 2-TRANSFER, and 3-COMPLETE.

 

[Bob Phillips]

Mike,

Aladin has provided an answer, but I noticed the statement '... value to
stay at whatever it is ...'. As this is a formula, there is no value, you
have to set it. For instance, if a cell has a value ABC and you then put a
formula in that cell, the ABC is gone, and will only return if your formula
sets it. The formula cannot determine what was there and dynamically reset
that. Aladin has chosen to set it to "" if A3=3.

--

HTH

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

 

[miker1999]

Thanks for the reply...

so how can I accomplish what I am trying to accompish? Help...

 

[Bob Phillips]

Mike,

You can do it with VBA code. The following code handles those 2 tests but
can easily be extended to handle more. Put the code in the worksheet code
module (right-click on the sheet name tab, select View Code from the menu,
and paste the code in)

Dim oldValue

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
oldValue = Target.Value
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value
Case "1-PENDING": .Value = "New Hire"
Case "2-TRANSFER": .Value = "Transfer"
Case Else: .Value = oldValue
End Select
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)