multiple dropdown list

**allgeef** · 03-21-2011, 02:28 PM

Is it possible to have a different dropdown in one cell depending on a reference in another cell? For example, if in column A I have a dropdown of 6 items containing the name of 6 different companies. In column B I have 6 different dropdowns with employee names, and which dropdown appears would depend on the company name selected in the column A dropdown. Thanks for your consideration. Regards, Gary

**NBVC** · 03-21-2011, 02:33 PM

Have a look here for what might be an easier alternative...

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

**Whizbang** · 03-21-2011, 02:35 PM

Oops. NBVC beat me to it.

**allgeef** · 03-21-2011, 09:38 PM

This is working well. It does create a follow up question. Using the example on the link, if I select "Fruit" from the first named list, and then "Apple" from the supporting name list we have a good match. If I leave "Apple" in the supporting name list, but change the word to "Vegetable" in the first named list the word "Apple" remains until I select a new product from the supporting name list (like "Cabbage"). In my case a worker can inadvertently leave "Vegetable" and "Apple" together generating an incorrect price. Is there a way to stop, block, and/or blank the supporting name list if the first named list is changed? Regards, Gary

**JBeaucaire** · 03-22-2011, 01:52 AM

There are two ways to deal with the situation, one is VBA to remove the values that no longer match when an earlier cell is subsequently changed, the other is to use conditional formatting to flag the cell as having an inappropriate value in it now.

Sample Files:

Dependent Drop Down Lists

The file called DependentLists3.xls shows both of these techniques in action.

**allgeef** · 03-23-2011, 08:01 PM

Jerry, I really like option 2 and 3, but have struggled with applying either one. Would you consider looking at what I've uploaded? It is part of a larger workbook so there are errors in some formula cells, but I do not believe it affects what we are doing here. It is narrowed down because the overall size is more than the allowed. The Yellow column in the range tab would be the primary dropdown; the green would be the second dropdown and the blue the third dropdown. I have the first and second working, but cannot get the third to work. I believe ultimately I would like to try the second option, but seems like I need to get by this step. Thanks for your consideration, Gary
(all that said, and I cannot figure out how to upload my sample from here, any suggestions?)