Nested IF too long - other suggestions?

E

ExchangeNewbie

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!!!
 
M

muddan madhu

try this

Put this formula in P1 =IF(R1<>"",INDIRECT(S1),"")

and use helping column S , In S1 put this formula ="Q"&R1
 
R

RagDyeR

Does this help:

=INDEX(Q:Q,R1)

?
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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!!!
 
M

muddan madhu

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 ).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top