I have a static "Roles" table on one side:

And a 2 columns "Name" and "Role" table on the other side:

Now I'd like to have two dropdowns:

• the first (Role) would be static (based on the first "Roles" table, easy to do with Data Validation)
• the second (Name) would be dynamic, querying the second table based on the first dropdown's choice

Example: if I choose Developer in the first dropdown, I'd like to have Suzie and Julie in the second dropdown.

Which gives me:

To make it a little more robust we can create three ranges that return each list.

I put the titles in D1:F1 and then used =FILTER($A:$A,$B:$B=D1)in D2 and drug it over to F2.

Then I create three named ranges. The name was the Role and the Formula was like =Sheet5!$D$2# respectively. I needed to add _ in place of the space.

Then in the formula for the data validation I used =INDIRECT(SUBSTITUTE($G1," ","_"))

That way I can now drag it down and it will refer to the cell in column G of the same row and change the list accordingly: