Dependent DropDowns in Excel

Sometimes you may have a need to create a dropdown list which should be dependent on another dropdown. For instance in the above example depending on the company that you choose (A, B or C) in the Company dropdown, the respective products appear in the products dropdown.

Trust me its not difficult but just a couple of logic combined together to make this dropdown pudding. Let’s do it.. Shall we?

Consider this data!

Companies with their product list. Download this data

Step 1 : Name the ranges

1. Select the data and go to ‘Create from Selection’ in the Formulas Tab
   • We would like to name all products with their respective company names. For example, all cells having products for Company A will be named as (Company_A).  Learn Cell Naming !
2. Check ‘Top Row’ only since names are kept in top row. You can check / edit all names in the Name Manager (Ctrl + F3) in the Formulas Tab

Step 2 : Create Data Validation Dropdowns

They are pretty simple. All you gotta do is

1. Go to the Data Tab and click on Data Validation or use the shortcut Alt A V V
2. Select List Option in the Data Validation Box
3. And link the source (the names of the companies) in the source field

Drop Down for Products

Now here is the catch 😯 We have to automate the dropdown to pick up the list of products of the company name that we select in the previous drop down. All we got to do is to use cell naming with a small formula in the data validation box. Here is how you do it

1. Repeat the Data Validation feature on this cell
2. Under the List option. Put the following formula in the source field =INDIRECT(SUBSTITUTE(C9,” “,”_”)) Some quick notes on the above formula
   • Substitute function will replace any spaces (in the previous dropdown) with an underscore, since spaces are not allowed in cell naming
   • The Indirect function will treat the company name as range of named cell address and will pick up the products of selected company
3. That’s it done!

Also learn

How to create Interdependent Dropdowns – The value selected in one dropdown should not appear in the other drop down