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"[TABLE="width: 369"]
<tbody>[TR]
[TD]Departement[/TD]
[TD][/TD]
[TD]Marketing[/TD]
[TD]Finance[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Christophe[/TD]
[TD]Tania[/TD]
[/TR]
[TR]
[TD]Finance[/TD]
[TD][/TD]
[TD]Pierre[/TD]
[TD]Laurent[/TD]
[TD]Aurélie[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD]Jean[/TD]
[TD][/TD]
[TD]Dona[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Alexandre[/TD]
[/TR]
</tbody>[/TABLE]
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.