3 tier drop down lists

[Bill Morris]

Hi

I am trying to create a 3-tier dependant dropdown list system

Dropdown 1 = Supplier (Many of)
Dropdown 2 = Material Type (Approx 20 of, but not all suppliers supply all materials)
Dropdown 3 - Material grade (Many of but Unique to supplier/material combo - eg Material grade ABC123 is Material Type 'ABS' which is supplied by 'iPolymers.com'.

Supplier A could supply many Material Types. Each Material Type may be available in a number of different grades.

I am trying to set it up so that I:

• select a supplier using Dropdown 1
• The Material Types they sell appear in Dropdown 2
• The Material Grades of Material Type selected from the selected Supplier appear in Dropdown 3.

Thanks if you have any idea how to tackle this.

 

[Maverick27]

A tough nut to crack, always wanted to know how to do this ?! I've done a look up for a Single Tier.

 

[Kamolga]

Hi,

The formula to use is the dropdown list is =indirect(A1) if list 1 is in A1

So that when I name John, Pierre & Jean as "marketing", Chrisophe and Laurent as "Finance" and Tania, Aurélie and Dona as "Sales"

Departement		Marketing	Finance	Sales
Marketing		John	Christophe	Tania
Finance		Pierre	Laurent	Aurélie
Sales		Jean		Dona
				Alexandre

<tbody>
</tbody>

if I select Marketing in dropdown 1, I have people from Marketing. For this list to be dynamic (increase when I add names), I use tables (insert menu then table) instead if lists.

In your case, because you have many suppliers and naming ranges would become a nightmare (unless you can easily put the data as in my simple example and have a macro to do it), I guess I would create a table (difficult to make it dynamic so count the maximum lines required) which would list the material available for the selected supplier to be used as list 2 (using vlookup or index,match,match depending on how your data is set up).
Another option could be to create a macro (private sub when using droplist 1) to update a pivot table with selected supplier in filter and that would give you available material for the supplier to insert in list 2; same for list 3.