Dependent Drop Down List with Hyphens

[jleigh37]

Hello,

I'm having trouble creating a dependent drop down list. I need to create a drop down list with a "department number - description"...which I did and it came out fine. Then I need to create a dependent drop down list that has "class number - description", however this is where I'm getting stuck. Here is my data. The first column is the Department drop down list and the next four columns would be dependent if I chose 100 - Tops, 200 - Dresses, ect. Any help would be greatly appreciated. I've tried reading other blog posts but can't seem to fix it?

Dept	Tops	Dresses	Bottoms	Accessories
100 - Tops	100 - Tops/BlousesTops	150 - Dresses	170 - Denim	210 - Accessories
200 - Dresses	110 - TanksTops	160 - Skirts	180 - Pants	230 - Intimates
300 - Bottoms	120 - TeesTops		200 - Shorts	240 - Shoes
400 - Accessories	130 - Sweaters/CardigansTops			250 - Jewelry
	140 - OuterwearTops

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Eric W]

Here's the easiest way I can think of to do it.

Let's say your data looks like this:

	A	B	C	D	E
1	Dept	Tops	Dresses	Bottoms	Accessories
2	100 - Tops	100 - Tops/BlousesTops	150 - Dresses	170 - Denim	210 - Accessories
3	200 - Dresses	110 - TanksTops	160 - Skirts	180 - Pants	230 - Intimates
4	300 - Bottoms	120 - TeesTops		200 - Shorts	240 - Shoes
5	400 - Accessories	130 - Sweaters/CardigansTops			250 - Jewelry
6		140 - OuterwearTops

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

Sheet3​

Names in row 1, data below. Now select columns A:E, then from the Formulas tab, Defined Names section, click on Create from Selection, check Top row and uncheck Left column. You have now just created a named range for each of those columns, with the name being the heading.

Now let's say your first drop down is in G1. Select G1, and from the Data Tab, Data Tools section, click Data Validation > Allow: List, and in the Source: box put: =Dept

And if your second drop down is in H1, Select H1, > Data Validation > Allow: List, and in the Source: box put:
=INDIRECT(MID(G1,7,99))

You're done! The MID in the formula is to skip past the "100 - " part of the Dept name in G1, otherwise the name does not match the names in B1:E1.

Let me know if this helps.

 

[jleigh37]

IT WORKED!!!! Thank you Eric!

 

[Eric W]

You're welcome!