| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF too long - other suggestions?
Our timesheet template asks for the Employee name in a pop-up dialog box. The
result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
try this
Put this formula in P1 =IF(R1<"",INDIRECT(S1),"") and use helping column S , In S1 put this formula ="Q"&R1 On Sep 15, 11:00*am, ExchangeNewbie wrote: Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,*Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
Thanks, but how does this address the nested functions?
"muddan madhu" wrote: try this Put this formula in P1 =IF(R1<"",INDIRECT(S1),"") and use helping column S , In S1 put this formula ="Q"&R1 On Sep 15, 11:00 am, ExchangeNewbie wrote: Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Â*Q7,IF(R1=8,Q8,Q9))))))) ) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
Try this
If you want nest more than 7 functions, then create named formula =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,* Q7,IF(R1=8,Q8,Q9)))))))) define this formula as "X" =IF(R1=9,Q9,IF(R1=10,Q10,IF(R1=11,Q11,IF(R1=12,Q12 ,IF(R1=13,Q13,IF(R1=14,Q14,IF(R1=15,Q15,Q16))))))) define this formula as "Y" then use =If(X,X,Y) make sure u check the option Accept labels in formula ( Go to tools | options | calculation tab | accept labels in formula ). On Sep 15, 11:24*am, ExchangeNewbie wrote: Thanks, but how does this address the nested functions? "muddan madhu" wrote: try this Put this formula in P1 =IF(R1<"",INDIRECT(S1),"") and use helping column S , In S1 put this formula ="Q"&R1 On Sep 15, 11:00 am, ExchangeNewbie wrote: Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,**Q7,IF(R1=8,Q8,Q9))))))) ) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!!- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
Does this help:
=INDEX(Q:Q,R1) ? -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ExchangeNewbie" wrote in message ... Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
WOW!!! That worked perfectly!!!!! Thanks!!!!
"RagDyeR" wrote: Does this help: =INDEX(Q:Q,R1) ? -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ExchangeNewbie" wrote in message ... Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "ExchangeNewbie" wrote in message ... WOW!!! That worked perfectly!!!!! Thanks!!!! "RagDyeR" wrote: Does this help: =INDEX(Q:Q,R1) ? -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ExchangeNewbie" wrote in message ... Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF too long - other suggestions?
=OFFSET(Q1,R1-1,)
"ExchangeNewbie" wrote: Our timesheet template asks for the Employee name in a pop-up dialog box. The result assigns a number to a cell. That result assigns the employee name, title, and employment status to different cells in the sheet using nested IF formulas. For some reason I cannot add a 10th, 11th, etc level to the nest even though Help says 64 levels are possible. =IF(R1=1,Q1,IF(R1=2,Q2,IF(R1=3,Q3,IF(R1=4,Q4,IF(R1 =5,Q5,IF(R1=6,Q6,IF(R1=7,Q7,IF(R1=8,Q8,Q9)))))))) Any suggestions? Can I use LOOKUP? If so how? Or is there something else?? Thanks!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Suggestions??? | Excel Discussion (Misc queries) | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| any suggestions? | Excel Discussion (Misc queries) | |||
| Need your suggestions | Excel Worksheet Functions | |||
| Suggestions Please | Excel Discussion (Misc queries) | |||
Hybrid Mode
