ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Nested IF too long - other suggestions? (https://www.excelbanter.com/excel-worksheet-functions/202577-nested-if-too-long-other-suggestions.html)

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

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

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



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

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

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




muddan madhu
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 -

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


All times are GMT +1. The time now is 05:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com