 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008If Statement
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. Have a look in the help index for VLOOKUP  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >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 [quoted text clipped - 9 lines] > 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. I have looked at it but that is new territory for me. Any suggestions? > Have a look in the help index for VLOOKUP > [quoted text clipped - 11 lines] > > 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. 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 >I have looked at it but that is new territory for me. Any suggestions? > [quoted text clipped - 17 lines] >> > "D". >> > I am loosing my patience on this one. Please Help. 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. > Basically, you build up a table somewhere on your worksheet, e.g. in X1:Y10 > and you just list the choices like this: [quoted text clipped - 39 lines] > >> > "D". > >> > I am loosing my patience on this one. Please Help. Try this: =IF('Week 2'!$E$20="Personal","PL",IF('Week 2'!$E$20="Leave W/O Pay","AD",LEFT('Week 2'!$E$20))) > 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 [quoted text clipped - 8 lines] > 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. 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 > Try this: > [quoted text clipped - 13 lines] > > 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. Try my formula it will work exactly what you want > 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 [quoted text clipped - 19 lines] > > > 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. 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))))))))))) > Try my formula it will work exactly what you want > [quoted text clipped - 21 lines] > > > > 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. 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))))))))))) > Try my formula it will work exactly what you want > [quoted text clipped - 21 lines] > > > > 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. No, No, No Just copy exact the formula I provided to you. It should work > 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 [quoted text clipped - 29 lines] > > > > > 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. 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. > No, No, No > Just copy exact the formula I provided to you. It should work [quoted text clipped - 32 lines] > > > > > > 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. 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 [quoted text clipped - 36 lines] > > > > > > > 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. I see that. Thank you. Please forgive my stuburness, you have been extremely helpful. Does the LEFT automatically use the rest of the list? > It's "magic" formula > [quoted text clipped - 38 lines] > > > > > > > > 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. Yes, it returns the first left character eg. =LEFT("cat food") it returns c > I see that. Thank you. Please forgive my stuburness, you have been extremely > helpful. Does the LEFT automatically use the rest of the list? [quoted text clipped - 41 lines] > > > > > > > > > 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. Thank You again.. > Yes, it returns the first left character > [quoted text clipped - 46 lines] > > > > > > > > > > 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. You are welcome! > Thank You again.. > [quoted text clipped - 48 lines] > > > > > > > > > > > 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. 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! SignatureBiff Microsoft Excel MVP >I see that. Thank you. Please forgive my stuburness, you have been >extremely [quoted text clipped - 59 lines] >> > > > > > > > "Jury Duty" as "D". >> > > > > > > > I am loosing my patience on this one. Please Help. Rocket science.<g> SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > It's "magic" formula > [quoted text clipped - 52 lines] >> > > > > > > Duty" as "D". >> > > > > > > I am loosing my patience on this one. Please Help. 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)) > 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 [quoted text clipped - 29 lines] > > > > > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.