| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement
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" wrote: 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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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" wrote: 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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
Try my formula it will work exactly what you want
"ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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))))))))))) "Teethless mama" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
No, No, No
Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
It's "magic" formula
"ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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" wrote: It's "magic" formula "ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
Yes, it returns the first left character
eg. =LEFT("cat food") it returns c "ppidgursky" wrote: 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" wrote: It's "magic" formula "ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
Thank You again.. "Teethless mama" wrote: Yes, it returns the first left character eg. =LEFT("cat food") it returns c "ppidgursky" wrote: 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" wrote: It's "magic" formula "ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
You are welcome!
"ppidgursky" wrote: Thank You again.. "Teethless mama" wrote: Yes, it returns the first left character eg. =LEFT("cat food") it returns c "ppidgursky" wrote: 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" wrote: It's "magic" formula "ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
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! -- Biff Microsoft Excel MVP "ppidgursky" wrote in message ... 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" wrote: It's "magic" formula "ppidgursky" wrote: 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" wrote: No, No, No Just copy exact the formula I provided to you. It should work "ppidgursky" wrote: 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" wrote: Try my formula it will work exactly what you want "ppidgursky" wrote: 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" wrote: 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" wrote: 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. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Statement
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
| If Statement | Excel Worksheet Functions | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
| Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Linear Mode
