Drop Menus that Filter Subsequent Drop Down Menus

[Roly1]

I have a database of clients with unique company names as well as unique sales reps. For quoting, we currently have a drop menu and we need to scroll through all the clients in the database. I’d like to know how to filter out non-relevent entries using a series of drop menus.

On the quoting sheet, I’d like to be able to first select the sales rep, then we will see a drop menu with only the companies they are working with, and then they will see a drop menu of the various contacts within that company. Once the three menus have been selected, the other cells will have a LOOKUP function that will fill in their phone numbers, email, address, etc…

I’ve attached a sample of what I’m looking for. It has a sheet called “contractor list” I’d like it to filter through.
Sample Problem - Drop Menu Filtering.ods

[SM_Riga]

Hello @Roly1

You can use a Data -> Validity... tool to define allowed entries for each of the three cells where each cell value is based on the previous cell selection. Please find an example spreadsheet attached. It is based on your sample file, I just added some named ranges for better readability and easier understanding what does formulas do. Click on the B1, B2 or B6 cells on the Form sheet and go to Data -> Validity... menu to see an example of using this feature

Here you can find an article with more detailed explanation on creating dependent validity lists.

Edit 09.01.2019 - updated sample added.

[Roly1]

Wow. That’s awesome! Thanks for doing that for me SM_Riga. It looks like it works great. I haven’t had the opportunity to implement this yet, but from what I see it will work fine and does exactly what I asked for.

One quick question… Is there a way to do this that doesn’t require you select the drop menus in a specific order? The example spreadsheet is set up that you need to select Sales Person—> Company----> Company Contacts. The ideal scenario would be that if I choose the company contact first or the company first, it would automatically generate the results in the other cells?

Is that too much to hope for

[SM_Riga]

I have added updated spreadsheet, where you can select categories before performing data filtering.