What Is Dynamic Dependent Drop-Down List?
A drop-down list is a set of predefined values from which you can choose a specific value for a cell. A dynamic dependent drop-down list consists of two parts:
- Primary Drop-Down List (1st List): This is the initial drop-down list where you select a category or type. For example, you might choose Poetry or Science Fiction.
- Secondary Drop-Down List (2nd List): Based on your selection from the primary drop-down list, the secondary drop-down list automatically updates to show relevant options. For instance, if you choose Poetry, the second list will display book names related to poetry.
Method 1 – Using Formulas to Create a Dynamic Dependent Drop-Down List
We’ll assume you have a dataset containing two columns: Book Type and Book Name.
Step 1 – Creating the Primary Drop-Down List
- In Cell E4, enter the following formula to create the list of unique book types:
=UNIQUE(OFFSET(B5,0,0,COUNTA(B:B)-2,1))
- Press Enter, and an array of book types will be generated.
- Select the cell where you want to create the primary drop-down list (e.g., Cell E12).
- Go to the Data tab and choose Data Validation.
- In the Data Validation window:
- Select List in the Allow section.
- In the Source box, enter $E$4#.
- Press OK. You’ll now see a drop-down list in Cell E12, allowing you to select different book types.
- We will see a drop-down list in Cell E12. We can select different Book Types from there.
Step 2 – Inserting the Second Drop-Down List
- In Cell F4, enter the following formula to create the list of Book Names based on the selected book type:
=FILTER(C:C,B:B=E12)
- Press Enter, and the Book Names related to the chosen book type will appear.
- Select Cell F12 and go to the Data tab.
- Choose Data Validation.
- In the Data Validation window:
- Select List in the Allow section.
- In the Source box, enter $F$4#.
- Press OK.
- Now you have a dynamic dependent drop-down list.
- If you change the selection in the first drop-down list, the content of the second drop-down list will automatically update to match the chosen book type.
Read More: How to Create Dependent Drop-Down List with Multiple Words in Excel
Method 2 – Using Excel Table to Create Dynamic Dependent Drop-Down List
In this method, we’ll create a dynamic dependent drop-down list using an Excel table. For demonstration purposes, I’ve used different book names categorized under columns such as Novel, Autobiography, Science Fiction, and Poetry.
Step 1 – Creating the Primary Drop-Down List
- Create the Table:
- Select the entire dataset (including the book names and categories).
- Press Ctrl + L to create an Excel table.
- In the Create Table window, press OK.
- Generate the Primary List:
- Enter the following formula in Cell G4:
=TRANSPOSE(Table3[#Headers])
(Replace Table3 with the actual name of your table if it’s different.)
-
- The primary list (book categories) will be created in Cell G4.
- Data Validation for Primary List:
- Select Cell G10.
- Go to the Data tab.
- Choose Data Validation.
- In the Data Validation window:
- Select List in the Allow section.
- In the Source section, enter $G$4#.
- Press OK.
- Now you can select different book types from the primary drop-down list.
Step 2 – Generating the Second Drop-Down List
- Create the Dynamic-Dependent List:
- Enter the following formula in Cell I4:
=INDIRECT("Table3["&G10&"]")
-
- Press Enter, and the second list (book names related to the selected category) will appear in Cell I4.
- Data Validation for Second List:
- Select Cell G13.
- Go to the Data tab.
- Choose Data Validation.
-
- In the Data Validation window:
- Select List in the Allow section.
- In the Source section, select Cell $I$4 with a # sign at the end.
- Press OK.
- In the Data Validation window:
- You will see the drop-down list in Cell G13.
- Now you have a dynamic dependent drop-down list. If you change the selection in the first drop-down list, the content of the second drop-down list will automatically update to match the chosen book category.
Read More: Create Excel Drop Down List from Table
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- Conditional Drop Down List in Excel
- How to Use IF Statement to Create Drop-Down List in Excel
- Excel Dependent Drop Down List
- How to Make Dependent Drop Down List with Spaces in Excel
- Create Excel Filter Using Drop-Down List Based on Cell Value
- Excel Formula Based on Drop-Down List
- How to Extract Data Based on a Drop Down List Selection in Excel
- How to Populate List Based on Cell Value in Excel
- How to Change Drop Down List Based on Cell Value in Excel
<< Go Back to Excel Dependent Drop Down List | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi, I’m curious if you have to leave the full list of items used for the drop down menus visible? I’m trying to use this feature on an order form to eliminate typos and errors. Is it possible to create an order form with the drop down menu and not have the information visible at all times? If not, what would be the best way to accomplish this?
Hi, you can learn about how to hide source data from this article.
Hi, is there a way to make the results of these lists editable?
not the list itself but the results, e.g, if i click novel and then it populates with A Tale of Two Cities, can I then edit the column with the book title in it?
So that every time it filters, we can have most of the same info, but a few things need to change, such as sizes etc, as we have parts but they aren’t standard, they all vary, and there are hundreds, too many to make drop down list out of. can this be done?
Thanks
Hello Missy, If you change the list it will change the results, both are the same. Your question “can I then edit the column with the book title in it?” is not clear to us. It will be better for us, if you create a sample Excel file of what you want and share it with us. Thanks.
Hi
how do i add a sample workbook for you to see?
Thanks
Hello, Missy! Please send us your problems here: [email protected]