Dynamic Dependent Dropdown Lists that populate based on matching rows within table

So, I've spent the better part of a day working on this to no avail. I'm trying to figure out a way to do dynamic dependent dropdown lists without using macros.

Here is my case:

I have a table full of products with fields like supplier, category, item description, and pricing.

I have a separate table that the user will be manipulating to create a Job Estimate. The idea is that the user adds items to the job estimate from the products table by using drop-down menus. Since the products database has hundreds of entries, I want to use cascading dependent dropdown lists to populate the job estimate table.

The flow should be as follows:

• First column: User selects a supplier from the dropdown list of suppliers
• Second column: User selects a category from a dropdown list that is dynamically populated based on the choice made in the 1st column
• Third column: User selects an item from the dropdown list that is dynamically populated based on the choices made in the first two columns

Note: this essentially means that all the cells in the first column have data validation of supplier names, but all the rest of the cells in the job estimate table have data validation that is dynamically populated based on the values in the adjacent cells.


I think what my question boils down to is:

Is there some way that I can retrieve the reference to the intersection of matching rows in a table and a certain table column. i.e. returning by reference the intersection of, say, rows 9 and 10 with column C.

I've attached a spreadsheet that represents a basic version of what I want that shows the desired workflow.

Any insights would be greatly appreciated!

Mac,

This should get you started. Maybe someone else can pick up from here. What I do not know how to do is to have the items dependent on both supplier and category. I basically only know how to have it work where all electrical items are available, regardless of the supplier.

You should like at the named ranges I defined and then also look at the data validation references to see how I did this.

You will notice the setup for the data validations is on the "lists" sheet.


Cascading Dropdowns.xlsx

Have added a lists sheet and named ranges.

Used the indirect function and substitute function in the data validation.

Just adjust ranges to suit your needs.

Windy