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

mikeyank

New Member
Joined
Jun 12, 2009
Messages
16
Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Here is my problem: <o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
=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:p></o:p>
<o:p></o:p>
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:p></o:p>
“The List Source must be a delimited list, or a reference to a single row or column”<o:p></o:p>
<o:p></o:p>
What should really happen is this:<o:p></o:p>
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only. <o:p></o:p>
<o:p></o:p>
<o:p>
excel.jpg
</o:p>
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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...
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
GECS, you brought up a totally valid point!:eek:
I am actually working on error messages that would pop up in case that happens. How do YOU think I should handle this?:confused:
Thanks
 
Upvote 0
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).
 
Upvote 0
Sorry, I can't help you with this in Excel, but I just wanted to point out how easy this is in Access :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top