How to create a drop down list based on the choices of another drop down list?

**Happy jain** · 06-27-2013, 12:39 AM

I want to create an drop down list based on the choices selected in previous cell. And on basis of the options selected in then previous drop down list, I want to reduce the choices available in this new drop down list.
How to do that?

**isskint** · 06-27-2013, 01:21 AM

Where are these drop down boxes? On a form, on a sheet or in a cell (data validation - pick from list)??

Also some sample data may help identifying the best solution for your scenario.

**Happy jain** · 06-27-2013, 01:26 AM

Its in a cell. And i have to create a drop down list based on the data validation in the previous list.
Also the previous list is located in the same worksheet in the previous Row.

**isskint** · 06-27-2013, 01:54 AM

The simplest way to do this is with named ranges. On a separate tab build your lists of data and name the ranges they occupy.

EG
Food Type
Burger
Pizza
Curry
So highlight the 3 food types and name the range FoodTypes

Then create 3 lists for the types -
Burger
Qtr Pounder
Half Pounder
Qtr Pounder + Cheese

Pizza
Deep Pan
Thin Base
Stuffed Crust

Curry
Vindaloo
Dhansak
Jalfrezi

Again highlight the 3 data (not the heading) and name the range Burger Pizza Curry - must be the same as the spelling in your first list.

Set the list source for the first data validation list as =FoodTypes

set the list source for the second data validation list as =INDIRECT(cell ref) - for cell ref that would be the first cell in the FoodTypes column.

here is a good link on the subject

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