Nested level limit exceeded with IF statements

**SimonDorfman** · 11-02-2011, 12:51 PM

This formula works:

Please Login or Register  to view this content.

This formula doesn't work:

Please Login or Register  to view this content.

...because of error: "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

I need to continue the above logic all the way down to "ZZ"

Please Login or Register  to view this content.

Any ideas?

Thanks!

UPDATE:
Sample data excel file uploaded a couple posts below this one: http://www.excelforum.com/2633576-post4.html

**Whizbang** · 11-02-2011, 01:42 PM

Please read this article. It will help you immensely.

http://www.excelhero.com/blog/2010/01/i-heart-if.html

I imagine a lookup function will work here.

In cell B62, enter this formula then copy down to B113:
=A62<>A63

Then fill C62:C113 with your results (e.g. A,B,C,...XX,YY,ZZ)

Then use this formula anywhere on the sheet to return the appropriate value:
=VLOOKUP(TRUE,B62:C113,2,FALSE)

See the attached.

**arthurbr** · 11-02-2011, 03:40 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**SimonDorfman** · 11-02-2011, 03:57 PM

Thank you both for your help. I've read the article linked above ( http://www.excelhero.com/blog/2010/01/i-heart-if.html ) and I'm still scratching my head trying to figure out how to make it work for my situation. I've created a sample data file and attached it to this post. I hope this will help you guys get a more clear picture of the problem at hand. Thanks!

**shg** · 11-02-2011, 04:43 PM

I expect this could be simplified, but in B2 and copy down,

=SUBSTITUTE(IF(A2<>A1, "A", ADDRESS(1, COLUMN(INDIRECT(B1 & 1)) + 1, 4)), "1", "")

**SimonDorfman** · 11-02-2011, 05:14 PM

Originally Posted by shg

I expect this could be simplified, but in B2 and copy down,

=SUBSTITUTE(IF(A2<>A1, "A", ADDRESS(1, COLUMN(INDIRECT(B1 & 1)) + 1, 4)), "1", "")

Wow, it's like magic. I'll have to read up on how INDIRECT, COLUMN, ADDRESS & SUBSTITUTE work. I want to understand how this works.

Unfortunately, it doesn't quite work. When it gets to Z, instead of continuing with AA, BB, CC, it continues with AA, AB, AC, etc.

Is there anyway to change that?