Maxing out nesting levels limitation of IF function

[Sergei]

I have a question on creating a drop-down menu in the cell that would check
the contents of the other cell first, then based on what it finds there, it
will display the appropriate list in the drop-down. I am able to do this
using nested IF formula. However, nesting levels limitation in Excel 07 is
64, I have about 71 lists to display based on the contents of that other cell.

I tried vlookup with arrays, but Data Validation interface does not allow
using arrays.

Is there any other way?

Thank you!

 

[bpeltzer]

My approach to a similar situation is based on having one complete list of
the potential drop-down values, plus a three-column table. The table's
columns contain the 'parent cell' setting, the point in the list at which the
associated drop-down values begin and the number of such values. Then I have
a defined name using the offset function and the values from columns two and
three of the table; the defined name is my validation list.
Ex
Apple 1 2
Tree 3 3

Granny Smith
Macintosh
Elm
Maple
Oak

So if Apple is selected, my FirstRow is 1, RowCount is 2, and my ValidList
is =offset(A1,FirstRow-1,0,RowCount,1)

 

[Ashish Mathur]

Hi,

you may want to try out the following solution on Debra's site.

http://www.contextures.com/xlDataVal02.html

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Sergei]

You gave me a good start! I then combined OFFSET with VLOOKUP and also came
up with the way to update the original lists with minimum disruptions to the
model. It now works perfectly!

Thanks a lot!