Nested IF Conditions

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

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

--
Don Guillett
SalesAid Software
[email protected]
"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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"))



In article <[email protected]>,
"prkhan56" <[email protected]> wrote:

> 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

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


--
Don Guillett
SalesAid Software
[email protected]
"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

--
Don Guillett
SalesAid Software
[email protected]
"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> 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
>