Nested IF too long - other suggestions?

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

 

[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

 

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

 

[ExchangeNewbie]

Thanks, but how does this address the nested functions?

 

[Teethless mama]

=OFFSET(Q1,R1-1,)

 

[ExchangeNewbie]

WOW!!! That worked perfectly!!!!! Thanks!!!!

 

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

 

[RagDyeR]

You're welcome, and appreciate the feed-back.