excel drop down corresponding to a mutual cell

Question

Trying to accomplish this without using VBA - I have two sets of data: Categories and SubCategories both which include a name/description as well as a number to identify them. I have a dropdown using Data Validation for the categories which uses a VLOOKUP to pull in the corresponding number for that category. I'm now trying to create a second drop down which will show me subcategories but only to their corresponding categories. The list of categories and subcategories are listed on separate sheets. The subcategory also has a 3rd column which lists the category number. See below for an example.

SubCategory SubCatNumber    Category Number
Carrots     00              10
Celery      02              10
Onions      03              10
Apples      00              12
Bananas     02              12
Cantaloupe  03              12

So the drop down for the sub cats would show me Carrots, Celery and Onions when I choose Category 10 from my existing drop down. I've tried using another VLOOKUP as well as a combination of INDEX and OFFSET but can't seem to get this. Any help would be appreciated.

Variatus Variatus · Accepted Answer · 2017-05-15T03:49:41

Link each of your dropdowns to a named range, so that whatever is in the cells of the named range will appear in the dropdowns.

Create ranges (to name them will be easier) for each of the subcategories and fill them with the correct choices.

Finally, link the range linked to the sub-category dropdown to the named range defined by the selection in the main category dropdown.

Here are two examples, both based on the named ranges shown in this picture

Example 1 requires a little code.

Example 2 works without code.

excel drop down corresponding to a mutual cell

2 Answers