Excel - Dynamic Dropdown List

Question

Looking to make a dynamic dropdown list in a table. The photo link below should help visualize the problem.

What I am looking to do is create a dynamic dropdown list in one table using a column from the same table and two columns from a second table in another sheet.

The column with the dropdown should compare the column to the left of it to right column of the second table. The options in the dropdown should be the values in the left column of the second table if the cell to the left of the dropdown cell equals the cell to the right of the value cell in the second table.|

The photo below shows the table setups and the numbers next to the top left table show what options that should be in the dropdown.

Problem Example

Edit: I was looking to do this without using Visual Basic, but I would be open to it if it were the only option.

You need to setup helper column to filter data first, then use validation to pull dropdown. — Harun24HR
See my answer and follow every step carefully. Note it will work only on Excel365. — Harun24HR

teylyn teylyn · Accepted Answer · 2021-02-09T03:43:19

This is called dependent data validation. You need to set up range names. One for each reference, and an additional one to resolve the reference into a dropdown using Indirect. I set up these four range names and for the data validation I used List > =MyDropdown

a   =Sheet1!$H$2:$H$5
b   =Sheet1!$H$6:$H$8
cc  =Sheet1!$H$9
MyDropdown  =INDIRECT(Sheet1!$A2)

Note that the reference inside the INDIRECT() function is relative to the cell that was active when the reference is defined. Select cell in row 2, then create a new named range with that formula. If you select another cell, this will not work.

Note also that the named range cannot be called 'c', so I had to use another reference name.

Excel - Dynamic Dropdown List

2 Answers