Nested IF Conditions

[prkhan56]

Hello All Gurus,

I am using Windows XP/Office 2003 and have the following problem:


I have a worksheet with 9 Columns as follows:
A B C D E F G H I
Code Shift Work From To OTfrom upto Normal Extra
A xxx xxx xxx xx xxx xx (blank) (blank)
S xxx xxx xxx xx xxx xx (blank) (blank)

M xxx xxx xxx xx xxx xx 9 (blank)
H xxx xxx xxx xx xxx xx 9 (blank)

(Blank)xxx xx xxx xx xxx xx 9 3

Codes used are as follows:

A = Absent
S = Sunday

M = Medical
H = Holiday

My problem is as follows..
Normal case there is no entry in the Code Column A. If I type from the
above available Codes (A,S,,M or H) in Column A the respective Normal
(Column H) and Extra (Column I) should display the value as shown
above.

I tried following formula in Column H: which gives an error

=IF(AND(c9="",C9=""),"9",IF(AND(c9="A",C9="F","",IF(and(c9="M",
c9="H")," "," ")))

Can anyone give me a clue

Thanks in advance

Rashid Khan

 

[Don Guillett]

One way if you use data validation to restrict to your allowables
=LOOKUP(K15,{"a","h","m","s"},{"aaa","hhh","mmm","sss"})

 

[Don Guillett]

On re-thinking I would do this
right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
Select Case UCase(Target)
Case "A": x = "aaa"
Case "M": x = "mmm"
Case Else: MsgBox "Enter A, H, M, or S"
End Select
Target.Offset(, 7) = x
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
Select Case UCase(Target)
Case "A","S": x = "aaa"
Case "H","M": x = "mmm"
Case Else: MsgBox "Enter A, H, M, or S"
End Select
Target.Offset(, 7) = x
End Sub

 

[JE McGimpsey]

If I understand you correctly, one way:

H9: =IF(OR(A9="",A9="M",A9="H"),9,"")

or perhaps


H9: =IF(OR(A9="",A9="M",A9="H"),9,IF(OR(A9="A",A9="S"),"","Error"))

 

[Ragdyer]

If I understand what you're looking for,

Enter this in H2:
=IF(OR(A2={"M","H",""}),9,IF(OR(A2={"A","S"}),"","WrongCode"))

And, enter this in I2:
=IF(OR(A2={"A","S","M","H"}),"",IF(A2="",3,"WrongCode"))

Select both cells, and copy down as needed.

 

[prkhan56]

Hi there,
Thanks a lot.. your second suggestion worked for me perfectly..

Also thanks to Don for his suggestion

You guys are a great help to us

Rashid

 

[Don Guillett]

Now that I have seen the other answers,if you want the automatic sub without
formulas, try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
Select Case UCase(Target)
Case "A", "S": x = "": y = ""
Case "H", "M": x = 9: y = ""
Case " ", "": x = 9: y = 3
Case Else: MsgBox "Enter A, H, M, or S"
End Select
Target.Offset(, 7) = x
Target.Offset(, 8) = y
End Sub