Data Validation with nested IF statements or VLOOKUP does not work.

[mikeyank]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Here is my problem: <o></o>
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box. <o></o>
<o></o>
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:<o></o>
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))<o></o>
<o></o>
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:<o></o>
“The List Source must be a delimited list, or a reference to a single row or column”<o></o>
<o></o>
What should really happen is this:<o></o>
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only. <o></o>
<o></o>
<o>

</o>

 

[Jonmo1]

You can try Index

=INDEX(F5:I14,B4,0)

Where B4 is the First Validation Cell
And provided that first validation list is LITERALLY numbers 1 2 3 4 etc...

 

[sanrv1f]

have a look at this

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

 

[gecs]

For the second Data Validation list try:
=OFFSET(INDIRECT("$F$"&(MATCH($B$4,E4:E13,0)+3)),0,0,1,COUNTA(INDIRECT("$F$"&(MATCH($B$4,$E$4:$E$13,0)+3)&":$K$"&(MATCH($B$4,$E$4:$E$13,0)+3))))

Another (better) way is to define names and an association list for the values in the first list and the names of the second level, then use indirect and vlookup for the formula of the second Data Validation list. You can do this in another worksheet because the names with a Workbook scope can be used in a Data Validation formula in another sheet than the ranges associated to those names.

 

[mikeyank]

Thank you guys for quick responses!
I went through the article that sanrv1f pointed to, and that seemed to help me solve my dilemma. I got it to work with INDIRECT function! Thanks sanrv1f!

 

[gecs]

There is another catch in using structured lists: have you ever asked yourself what happens when you change your option on the first level of the structured option lists and forget to make your choice from the second list? In your case, supposing you have chosen "3" from the first list, then "door" from the second list and later you change your option for the first list and instead of "3" you choose "5", but you forget to make your selection from the second list. If you use the selected values from both lists in further processing what happens? Can you deal with the situation when a value in the second list is selected, but doesn't relate to the value selected from the first list?

 

[mikeyank]

GECS, you brought up a totally valid point!
I am actually working on error messages that would pop up in case that happens. How do YOU think I should handle this?
Thanks

 

[gecs]

Use the Worksheet_Change event to test for a change of the value of the cell housing the first Data Validation value (A4 according to the posted example) and change the value of the subsequent level of the list structure (A6) to an empty string. Then you'll only need to test if the value of the second structure level is empty or not (isblank(A6) or len(A6)=0).

 

[dsg1480]

Sorry, I can't help you with this in Excel, but I just wanted to point out how easy this is in Access

 

[gecs]

Sorry, I can't help you with this in Excel, but I just wanted to point out how easy this is in Access

I'm sure you can define "easy" in Access, but can you define it in Excel?
Sometimes the shortest way between two points is not the straight way, but the way you know, and if you do it for others is the way they know.