Creating multiple linked drop down menus from a database

[issac]

Hi

I'm a trader and I have a database of products from different companies that i trade. My objective is to create quotations easily from this database.

Basically i have 2 sheets. One is the master database sheet with the format (Company, Product Category, Product Name). There are 1000s of line items. The data in this sheet keeps dynamically increasing as and when new products get added to the portfolio.

The second sheet is the quotation sheet which also has the same format which I want to fill up using linked drop downs. For example, if under the Company name, i select Company 1, the drop down in the next column (product category) should show only the product categories which come under Company 1. and if i select a product under product name say Product Cat1, the drop down in the next column Product name should show only the product names for the combination of company 1 and product cat 1.

As of now, I'm not in a position to change the structure of columns in my master database sheet. if someone can guide me on how to set this up in excel that'll be really helpful.

Additionally, if these drop downs can be made like google auto complete instead of having to scroll down the entire drop down list, will be super helpful if someone can share that as well.

Thanks a ton,

Rgds,

Issac

 

[DHayes]

Hi Issac,
I will try my best and explain.
step 1.
you need select all the products from company A and give it a name. e.g CompanyA (Do not use spaces as this can cause a problem)
Do this for all the companies for your first selection drop down.
step 2
for your next drop down you still use list. but for your source put in the below formula.

=INDIRECT(SUBSTITUTE(A1," ",""))

A1 is you company dropdown.

I hope this will help you.