If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Maxing out nesting levels limitation of IF function
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! |
#2
|
|||
|
|||
Maxing out nesting levels limitation of IF function
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) |
#3
|
|||
|
|||
Maxing out nesting levels limitation of IF function
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" wrote in message ... 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! |
#4
|
|||
|
|||
Maxing out nesting levels limitation of IF function
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! "bpeltzer" wrote: 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) |
Thread Tools | |
Display Modes | |
|
|