Multi level dependantly linked drop down boxes

**MattHutchings** · 08-09-2012, 05:11 PM

Hi

I am trying to create a 3 level drop down set. Thand we'd to be hierarchical and allow the user to select the product by going through the higher levels of the hierarchy.

Level 1 is the business unit
Level 2 is the product category
Level 3 is the product

The user will start with selecting level 1 which will dictate what is selectable in level 2. Then, from selecting level 2 the options for level 3 options are presented.

Some example data below (fictitious data obviously - and sorry, I haven't eaten yet and am hungry as you can see!)

Dairy, Creams, Low fat cream
Dairy, Creams, Full cream
Dairy, Milks, Semi-skimmed milk
Dairy, Milks, Skimmed milk
Meats, Beef, Beef steaks
Meats, Beef, Beef cubes
Meats, Chicken, Chicken fillets
Meats, Chicken, Chicken wings

Any suggestions on how to do this? Have seen how to do it for 2 levels with name indirect name ranges ut the setup of the sheet would be enormous for 3 levels - especially when there are over 20,000 products!

Thanks in advance!

**tigeravatar** · 08-09-2012, 06:00 PM

MattHutchings,

Welcome to the forum!
Attached is an example workbook based on the criteria you described. (I kept all formulas to 2003 because I saw that you have 2003 listed as your version of Excel. These formulas are inefficient and you could use much faster formulas with 2007 or higher.)

The first thing I did was setup the data table, which is the first worksheet named 'Data'.
Row 1 contains column headers, so actual data starts in row 2.
Column A is Business Units
Column B is Product Category
Column C is Product

Then I created dynamic named ranges for each of those columns:

All_BusinessUnits (column A):
```
Please Login or Register  to view this content.
```
All_ProductCategories (column B):
```
Please Login or Register  to view this content.
```
All_Products (column C):
```
Please Login or Register  to view this content.
```

That way, when data is added or remove to the data table, the dynamic named ranges will automatically pick them up.
With that setup, we can get the unique list of dependent items.
I put those in the Data tab also (starting at column E)
Column E is the Business Unit List
Column F is the Product Category List
Column G is the Product List

Before I could really move forward, I had to setup the sheet where the drop-downs will be located.
So I made a second sheet named 'Drop Downs'
Column A is for the drop-down title, and column B will contain the drop downs in B1:B3 for Business Units, Product Categories, and Products respectively.

Now I can create the formulas to get the unique list of dependent items in the Data sheet.

In cell E2:
```
Please Login or Register  to view this content.
```
In cell F2:
```
Please Login or Register  to view this content.
```
In cell G2:
```
Please Login or Register  to view this content.
```

I only copied the formulas down to row 5 because I didn't need to go farther than that with the sample data set. Copy the formulas down as far as you will need for the actual workbook.

Next is to make dynamic named ranges for those columns in order to get the lists for the data validation:

List_BusinessUnits (column E):
```
Please Login or Register  to view this content.
```
List_ProductCategories (column F):
```
Please Login or Register  to view this content.
```
List_Products (column G):
```
Please Login or Register  to view this content.
```

Lastly is to use the dynamic named ranges as the data validation lists, So on sheet 'Drop Downs' are these data validation formulas:

In cell B1 (Business Units):
```
Please Login or Register  to view this content.
```
In cell B2 (Product Categories):
```
Please Login or Register  to view this content.
```
In cell B3 (Products):
```
Please Login or Register  to view this content.
```

Hopefully you can adapt what is shown here to your actual workbook. Good luck!

**JBeaucaire** · 08-09-2012, 07:08 PM

The real need is a system that allows you to use the dependent links over and over again. For that, you need do a little more work yourself on your lists, but then the Data Validation formulas can do all the heavy lifting.

In this one, we only use one named range, that is your main listing. I used a dynamic formula so you can add as many main depts as you wish in column A without having to edit that.

Next are two pairs of columns. Column D is the categories you wish to have appear, but in column C, list the Dept that goes with each Category. When done, sort the two columns so all Dept items are together. Easy.

Next, another identical thing. IN column G are the individual products. List them, and include the Category they link to in column F. Again, always sort by column F after adding more items so all the categories products end up together.

That's it for that sheet. Now layout 3 columns on your main sheet.

A1: Business Unit
B1: Category
C1: Product

A2: Data Validation:
Allow: List
Source: =Main

B2: Data Validation:
Allow: List
Source: =OFFSET(INDEX(Data!$C:$C, MATCH($A2, Data!$C:$C, 0)),,1,COUNTIF(Data!$C:$C,$A2),)

You'll get an error, ignore it.

C2: Data Validation
Allow: List
Source: =OFFSET(INDEX(Data!$F:$F, MATCH($B2, Data!$F:$F, 0)),,1,COUNTIF(Data!$F:$F,$B2),)

Again, ignore the error if it occurs.

Now copy A2:C2 down so you have a bunch of rows setup.

Now use column A, then B, then C.