If Statement

[ppidgursky]

I am looking to add more items to a IF statement. The current is:=IF('Week
2'!$E$20="Annual","A",IF('Week 2'!$E$20="Sick","S",IF('Week
2'!$E$20="Personal","PL",IF('Week 2'!$E$20="FLSA Comp","F",IF('Week
2'!$E$20="GA Comp","G",IF('Week 2'!$E$20="Holiday","H",IF('Week
2'!$E$20="Pass","P",IF('Week 2'!$E$20="Leave W/O Pay","AD",""))))))))

I know excel says you can not use more than 7 functions in function. The
above function has 8 levels, and I need to add two more.
The current looks at a cell on 'Week2' and if the field displays "Annual"
for example, on the 2nd sheet it shows an "A". Using the same fields I want
to add 2 more; "Military Leave" to display as "M", and "Jury Duty" as "D".
I am loosing my patience on this one. Please Help.

 

[Don Guillett]

Have a look in the help index for VLOOKUP

 

[ppidgursky]

I have looked at it but that is new territory for me. Any suggestions?

 

[Pete_UK]

Basically, you build up a table somewhere on your worksheet, e.g. in X1:Y10
and you just list the choices like this:

Annual A
Sick S
Personal PL
FLSA Comp F
GA Comp G
Holiday H
Pass P
Leave W/O Pay AD

Then you would have one formula along the lines of:

=VLOOKUP('Week 2'!E20,X$1:Y$10,2,0)

and that will return the appropriate letter code.

Hope this helps.

Pete

 

[ppidgursky]

That worked perfectly. But one other thing. If field 'Week 2'!E20 is blank,
can I get it to produce a blank. I tried your example and used X1 and Y1 as
blanks, but it did not work. I guess I could type something there and change
the font color, but that is a cheap work around. Trying to get this right.

 

[Teethless mama]

Try this:

=IF('Week 2'!$E$20="Personal","PL",IF('Week 2'!$E$20="Leave W/O
Pay","AD",LEFT('Week 2'!$E$20)))

 

[ppidgursky]

I tried that, and that worked with the original 8 items, but I need to add 2
more to the list, and excel will not allow it. That hole cant add more than 7
funtions in a function thing. Looking at alternative methods, and the look up
works but I can't leave a blank. I am trying to mix the 2 together, if and
vlookup, and it works, but also will not give a blank, just a #N/A

 

[Teethless mama]

Try my formula it will work exactly what you want

 

[ppidgursky]

This is what I tried, and still received the error:
=IF('Week 1'!$E$21="Annual","A",IF('Week 1'!$E$21="Sick","S",IF('Week
1'!$E$21="Personal","PL",IF('Week 1'!$E$21="FLSA Comp","F",IF('Week
1'!$E$21="GA Comp","G",IF('Week 1'!$E$21="Holiday","H",IF('Week
1'!$E$21="Pass","P",IF('Week 1'!$E$21="Leave W/O Pay","AD",IF('Week
1'!$E$21="Military","M",IF('Week 1'!$E$21="Jury
Duty","J",LEFT('Week1'!$E$21)))))))))))

 

[ppidgursky]

This is what I wrote, and still received the error. Where am I going wrong?
=IF('Week 1'!$E$21="Annual","A",IF('Week 1'!$E$21="Sick","S",IF('Week
1'!$E$21="Personal","PL",IF('Week 1'!$E$21="FLSA Comp","F",IF('Week
1'!$E$21="GA Comp","G",IF('Week 1'!$E$21="Holiday","H",IF('Week
1'!$E$21="Pass","P",IF('Week 1'!$E$21="Leave W/O Pay","AD",IF('Week
1'!$E$21="Military","M",IF('Week 1'!$E$21="Jury
Duty","J",LEFT('Week1'!$E$21)))))))))))

 

[Teethless mama]

No, No, No
Just copy exact the formula I provided to you. It should work

 

[ppidgursky]

I have tried to combined the vlookup and the if statment to a blank. ie:
=IF('Week 1'!E20="","",VLOOKUP('Week 1'!E19,AL9:AM19,2,0))

 

[ppidgursky]

Well i'll be....I have been humbled.....
Now can you explain how that worked for the entire list, when only a few
were listed in the argument.

 

[Teethless mama]

It's "magic" formula

Well i'll be....I have been humbled.....
Now can you explain how that worked for the entire list, when only a few
were listed in the argument.

 

[ppidgursky]

I see that. Thank you. Please forgive my stuburness, you have been extremely
helpful. Does the LEFT automatically use the rest of the list?

 

[Teethless mama]

Yes, it returns the first left character

eg. =LEFT("cat food")
it returns c

 

[ppidgursky]

Thank You again..

Yes, it returns the first left character

eg. =LEFT("cat food")
it returns c

 

[Teethless mama]

You are welcome!

Thank You again..

 

[T. Valko]

Let's study the table Pete put together:

Annual A
Sick S
Personal PL
FLSA Comp F
GA Comp G
Holiday H
Pass P
Leave W/O Pay AD

=IF('Week 2'!$E$20="Personal","PL",IF('Week 2'!$E$20="Leave W/O
Pay","AD",LEFT('Week 2'!$E$20)))

The formula is using the logic of elimination to arrive at its result.

In the table there are 2 criteria that will return multiple characters,
Personal and Leave W/O Pay. All other criteria return the *first character*
of that criteria. So, instead of testing for each individual criteria the
formula tests for the 2 criteria that will return multiple characters. If
the cell doesn't contain either one of those then logic dictates that it
must** contain one of the other criteria for which we only want the first
character returned. So:

=IF E20 = Personal return PL, else
..... IF E20 = Leave W/O Pay return AD, else
........Return the *first character* of E20

** provided no other entries are possible!

 

[Don Guillett]

Rocket science.<g>