Using a Drop- Down list

Microsoft Excel 2007
February 3, 2010 at 10:06:39
Specs: Windows XP
I have been creating drop-down list by using the data validation function in excel. I am now going to create another drop-down list. However, I have quite a lot of items in this list and I am going to put them into different categories. For instance, I may have the categories of "food", "desserts", "beverages", "snacks". Under each category, there will be more than 10 items. In this case, is it possible for me to create a drop-down list so that users can first select the category, then choose the desired item?--Just like what I did when I was asked to categorize this question as related to "Mircosoft Office" under the larger set of "Office Software".

Thanks for your help in advance!!

See More: Using a Drop- Down list

Report •

February 3, 2010 at 10:29:11
The trick here is to use the INDIRECT function and Named Ranges.

In your first drop down (e.g. A1) you use your main categories:


In another list you put all of your "foods" and name that range "food"

The same for your list of desserts, beverages and snacks. Create the lists and name the ranges to match the proper category.

For your second drop down, the Source for the List under Data Validation is:


This will pull in the Named Range that matches the text in A1 and populate the dropdown with the list in that range.

P.S. Isn't dessert considered "food" ;-)

Report •

February 5, 2010 at 08:06:11
Thanks for your reply!
I tried your suggestions but it still didn't work! This was how I did it:
I created my first drop down by having the 4 main categories (The first category in cell F5)
then I created another list containing all the items and named all the items as different ranges, e.g. cells G5 to G8 as "Food", cells G9-G12 as "Beverages", etc.
Then I typed "=INDIRECT($F$5)" for the Source of the list under data validation.
When all these were done, I tried using the first drop down and it's fine but the second drop-down just contained all the items under the range "Food" no matter what I chose for the first drop-down.

Btw, is it possible for me to select the main category and then the items all in one cell?

Thanks again for your help!!

Report •

February 25, 2010 at 23:09:00
did you get the answer of your question? the solution given above works but if user copy the selected row and paste for adding new the sub items didnt get change. i am facing the same problem, please share ifyou have got any answer.

Report •

Related Solutions

February 26, 2010 at 05:09:17

I'm confused by your post.

First you said:

the solution given above works

Then you said:

i am facing the same problem

What exactly is the problem you are facing?

Please note: If your question does not relate to building dependent Drop Down lists, then please post your question in it's own thread. Thanks!

Report •

February 26, 2010 at 13:58:22
Actually it is very easy there is a step by step instruction in help. It is working well for me.

Report •

Ask Question