Interdependent Drop Down Menus

[Hangfire]

Hello Excel Experts

I'm trying to set up some drop-down menus, with the first one determining what will show in the second one. The first one (and I'll use the actual cells in my sheet so I don't get confused), located in cell E15 will show a range of numbers from 1 to 10 - defaulting to 1. The second drop down menu will only allow the user to pick a value of 0 if the first menu is showing from 1-3, a value of 0-1 if the menu is showing 4-6, 0-3 if the menu is showing 7-9, and finally a value of 0-5 if the menu is showing a 10. I hope that's somewhat clear.

After some searching around I came up with this for my Data Validation source:

=INDIRECT(VLOOKUP($E$15,SkillLookup,2,0)&"List")

The menus are on one sheet, and the formulas (for lack of a better word) are on another sheet - called Formulas. I've created ten named list of my corresponding ten possible choices from the first drop-down menu, named FirstList - TenthList inclusive. I've also created a table with the numbers one to ten listed next to the ten listed 'lists' - which I've named SkillLookUp. All of this is on the Formula sheet.

I am, of course, getting an error telling me that "The source currently evaluates to an error. Do you want to continue?" The second drop down will show nothing but a zero (which is what was typed in there before the drop down was created). I'm at a bit of a loss as to just how to go about fixing this - or really, what it is I've done wrong. I would have been more comfortable if I could have just put in a nice long, complicated, IF statement into the Data Validation box, but when I tried that it errored out too.

Thank you for any and all help.

 

[Ingolf]

Take a look here: http://www.contextures.com/xlDataVal02.html

 

[Hangfire]

Hey, Ingolf thanks for the link. That's actually the site where I got the formula that I'm using. I'll take another look, but I'm still not sure why the formula isn't working as intended.

 

[Ingolf]

Upload file to dropbox

 

[Eric W]

In theory, your process should work. Without seeing the actual details though, it's impossible to figure out what's wrong. However, the easiest way I know to set something like this up is as follows:

	I	J	K	L	M	N	O	P	Q	R
1	List1	List2	List3	List4	List5	List6	List7	List8	List9	List10
2	0	0	0	0	0	0	0	0	0	0
3				1	1	1	1	1	1	1
4							2	2	2	2
5							3	3	3	3
6										4
7										5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

On your Formulas sheet, set up your lists as shown. In place of the word "List", you can use any other word if you want (at least 4 letters, no spaces). Must be the same for all of them.

Now select cell I2. In the address box to the left of the formula bar, you'll see I2. Overtype that with List1. Repeat for J2 and K2 with List2 and List3. Select L2:L3, and type in List4. Repeat with M2:M3 and N2:N3 with List5 and List6. And so on.

(You could actually select columns I:R and click Create from Selection on the Formulas tab, but that leaves empty rows on the bottom.)

Now just go to the cell where you want the dependent drop-down to go. Click Data Validation > List > Source: =INDIRECT("List"&E15)

That's it. I created a version that only needs 4 lists, but it's more complicated.

 

[Hangfire]

Eric - that worked! Brilliant! Once I saw what you had done it all made sense. So simple and elegant, nicely done. I've been struggling with this for a while now and just couldn't see how to get it done - it's not something that I've ever tried to do before. I won't soon forget this lesson. Thank you again for your help. And thank you Ingolf for your help too.

 

[Eric W]

Glad we could help!