Dependent Drop Downs

Hi all,

I am trying to get some help. My knowledge of excel is capped at around VLOOKUP, so not too great.

I am trying to build a quoting system, whereby products can be found easily from supplier and category selections. As you can imagine suppliers will need to be electable for varying products, and most suppliers will have different category of products.

I currently can do this using the following table headers:

Supplier > Supplier_Category > Product Description/ Selection - this means that for each category of product i need to precede with the name of the supplier so that I don't have categories with the same name.[B]

Supplier Supplier A Supplier A_Boiler Supplier A_Flue Supplier B Supplier B_Boiler Suppler B_Flue
Supplier A Supplier A_Boiler Good Boiler Long Supplier B_Boiler 100 Boiler Long
Supplier B Supplier A_Flue Med Boiler Med Supplier B_Flue 200 Boiler
Supplier C Bad Boiler

This looks a bit clumsy though, having the name of the supplier alongside the category when a supplier has already been selected What would be great is to be able to have the following option:

Category >Supplier(same suppliers will be needed in different categories > Product Selection OR

Supplier > Category > Product Selection

I don't know if this makes sense to anyone!

Welcome to the forum.

This is a very regular query. Here's a pretty recent thread on the same issue: https://www.excelforum.com/excel-gen...on-a-list.html

Take a look at this file. Does this come anywhere close?? A sample excel file is hugely preferable to in-thread data

Can you tell me the pasword??

Noo!!!!! I have been working on it and don't intend to start all over again. I need the PW, ABSOLUTELY. You can PM it to me, if you feel you can't post it on the open forum..

OK. I have killed 95% of your Named Ranges... Sorry!!

I have created 4 new ones to replace them. They automatically adjust to take account of new entries for Job Type, Supplier, Category and Description. You'll find this MUCH easier to maintain (I hope...). You only have the thing populated for Valaint and BBS... so when you're looking at it, check only those ones.

If it's doing what you want, I can explain what you need to do to adjust the Named Range for Description to accommodate category/descriptions for the other suppliers. You will NOT need to create any more Nmed Ranges to accommodate these additions.

Play with it some more. I can then explain the next steps. It has another advantage that I forgot to mention... It avoids the use of INDIRECT, which can slow things up, as INDIRECT recalculates every time something changes ont he sheet.

I'm quite pleased with it, myself, TBH. I knew that the standard method for doing this was soooo limited and decided to come up with a better way of doing it, one rainy afternoon.

Mmmm. Try V2...

I tweaked this a little this morning. I went back to using Tables, but ONLY two additional ones. It'll make expanding this much easier.