# 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

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

E

#### ExchangeNewbie

Thanks, but how does this address the nested functions?

T

#### Teethless mama

=OFFSET(Q1,R1-1,)

E

#### ExchangeNewbie

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

M

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