Dependent Drop-Down in columns

[mark hansen]

I have set this up numerous times by searching for Dependent Drop-down list and following the instructions. This works well when using only two cells, one for the category, and one for the items in the category.

I'm having a hard time translating that when I want the category in column D and the items in the that Category in Column E. The category column (D) I can use a standard drop-down, but in the cell next to that, I don't know what to use for Data Validation.

We are on Excel 2010 and the category names have spaces in them.

Any help is appreciated.

Mark

 

[Nishant94]

Assuming you have data like this:

Book1
	C	D	E	F	G	H	I	J	K	L
1		Category	Items
2		Fruit			Veggies	Carrot	Potato	Cauliflower	Cabbage
3					Fruit	Apple	Grapes	Banana	Strawberry
4
5
6
7
Sheet3

In Column D you can use standard data validation and in Column E you can use this in Data validation after selecting List option in Data Validation:

=INDEX($H$2:$K$3,MATCH(D2,$G$2:$G$3,0),0)

 

[mark hansen]

Thanks for your quick reply Nishant!!! This works great. It looks like the "D2" in your formula is relative to the row you are on and when you start your data validation in E2, it looks on the same row, all the way down. I knew conditional formatting works like that, I didn't know Data Validation did that too.

You not only answered my question, but taught me a valuable concept.

Thanks Again!
Mark