|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 15th, 2008, 04:00 PM
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
September 15th, 2008, 04:16 PM
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
September 15th, 2008, 04:21 PM
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!!!
|
|
#4
September 15th, 2008, 04:24 PM
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!!!
|
|
#5
September 15th, 2008, 04:28 PM
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!!!
|
|
#6
September 15th, 2008, 04:44 PM
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
September 15th, 2008, 04:59 PM
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 -
|
|
#8
September 16th, 2008, 04:26 PM
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!!!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
