• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

limitation on nesting if statements in the Name Manger refers to

kelley

New Member
There appears to be a limit to how many If statements that can be nested in the Name Manger "refers to" box. It appears that number is 4 nested If statements. Is there any work around this limitation.
 
Kelley

What version of Excel are you using as from 2007 onwards there is no issues having more than 4 nested If's

Check that you have the right amount of brackets to close off the equation properly.


Depending on what you are trying to do, you could possibly use the 'Choose' statement as an alternative.
 
Hui

I am using 2007.

I know a regular formula now has a huge increase in nested if statements, but in the name manager function, there seems to have been failure to include the same increase in if statements.

I have never used the choose function, I will try that, it seems to be more flexible

thank you for your help.
 
Kelley

The following formula works in Excel 2003 as a named range

Code:
=IF(Sheet1!$A$1<1,1,IF(Sheet1!$A$1<2,2,IF(Sheet1!$A$1<3,3,IF(Sheet1!$A$1<4,4,IF(Sheet1!$A$1<5,5,IF(Sheet1!$A$1<7,7,IF(Sheet1!$A$1<8,8,IF(Sheet1!$A$1<20,20,9999))))))))

and it has 8 If's
 
Back
Top