HOW TO: Make a cell into a Drop-down combo-box in MS-Excel – Part 3

In the first two articles, we saw how to make a simple combo box in an Excel Spreadsheet and how to manage a long list of values in such a drop-down combo, respectively. Now we will see how we can make a drop-down combo whose values dynamically change based on another drop-down's value - like a Category - Sub-Category pair.

HOW TO: MS-Excel - Create a Dependent or Dynamic Drop-down combo-box

Many times we will need to make a drop-down dynamically show a list of values based on the item chosen in another drop-down combo box. Say, vehicle "Model" dependent on "Make" - BMW makes 3 series as Merc produces C-class. So when a user selects BMW as "Make", he should see only 3-series, 5-series etc. in the model. This can only happen if the "Model" drop down is dynamically set, when an user chooses the "Make".

First lets start with a sample spreadsheet with two columns, "Category" and "Sub-category". 

In the last article we saw how to make named ranges. In the same way make a named range, say "Category" as below.

Lets create a Drop-down combo for Category as described in previous article.

"Category" drop-down is ready.

To create dynamically updated dependent drop-downs we need to have named list of sub-categories. So, lets create them.

As you can see the sub-categories were named with the main category item values (i.e.) Fruits, Cars, Cities etc. This is an absolute need.

To create a sub-category item we use the same method as for the "Category" - Data -> Data Validation -> Lists. However the change is in what we specify for the Source parameter. We use the "INDIRECT" function on the related "Category" cell as below.

INDIRECT function converts the value specified in the "Category" cell (B2 for C2 cell), to the named range and send that as the "Source" parameter for the list. As this happens dynamically, you get a dynamically populated "Sub-Category" drop-down combo.

Now drag the cell C2 to the range required to get data validation copied to all the cells of the sub-category list.

A final check to see whether it works.

Job done!