Excel 2007 : Data validation drop-down list dependent on another cell

**lister000** · 05-12-2011, 09:44 AM

I have kind of a heirarchy of ID numbers that I'm working with... so if the ID is A, appropriate subcategory IDs would be 0,1,..,B.

I'd like to make a drop-down list of subcategory IDs given a particular ID. The way I have it set up now is that I have a sheet with, among other things, a column of As and a column of Bs where each row corresponds to an appropriate pairing:

ID SubID
12345 0
12345 1
12345 2
12345 3
23456 0
23456 1

So I can pull out the appropriate B given A easily enough from this list, but I'm having trouble coming up with a way to make the data validation drop-down list values from 0 to B. If there's a way to organize this sheet to make it more easy to make a validation list, I could do that too.

**Paul** · 05-12-2011, 06:26 PM

Hi Lister, welcome to the forum.

You can create dependent Data Validation lists in Excel. Here's a quick tutorial on how to do so:

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

Hope that helps!

**JBeaucaire** · 05-12-2011, 09:23 PM

I think I know what you're after, a single two-column set of data like you've shown. Then a selection from a drop down that lists the unique column A values then generates a SUB-data-validation list on the column B matches, all within the single table and all dynamic.

Yes?

I have a technique published here showing you a way to do it. It's complex but it works, there's a sample workbook where you can play with it and see it laid out before trying all the steps on your own workbook.
Sub DV Lists from a Larger DV List

If you'd rather keep it simple, then maintaining separate lists and using the contextures site is the way to go.