Creating Multi-Tiered List with Data Validation and INDIRECT

I found a great tip on one of my LinkedIn Excel Discussion groups. It was how to create a multi-tiered list.

The example used was state and city.  Using a data validation in Column A to list states, the person wanted the associated cities to display in Column B.  In other words, if Cell A1 displayed Indiana then B1 should display a list of related cities. If A1 changed to Massachusetts then the list of related cities should change to reflect that.

I thought I would share this tip with you but I wanted to take it a step further so that you can see how to do this using separate sheets and not have all the data in one sheet. I also decided to do a different example so that you would see some other uses for it.

We are going to create a Product List and as different products are selected, the associated model numbers will display.

Let's go through this:

I have 2 sheets - a Summary sheet and then a Model Information sheet.
Here is the sheet named Model Info. It contains the data we are going to work with.

Here is the Summary Sheet:

Put your cursor in cell A2 and then go to the Data tab and select Data Validation.

Select List from the Allow: dropdown
Put your cursor  in the Source: dialog box and select A1:A4 on the Model Info sheet.
Click OK.






The Summary sheet now has a drop-down arrow at A2.

Click in A2 and copy that blank cell with the drop-down arrow down to A5 or A6.
Click on A2 and select the drop-down arrow and select VCR from the list we just created.

What we want to do now is set up Column B so that it will display all the models associated with whatever is displaying in Column A.

Let's go to the Model Info sheet and create a range name for all of that data.
Select A1.I14 on the Model Info sheet. 
Click on the Formulas Tab and select Create from Selection.

Make sure that there is only a checkmark in left column and click OK.
This is an easy way to create multiple range names all at once. We are telling Excel to select the Left Column which in this case are the Product Line names in Column A and create that as the range name for all of the data to the right – the model numbers. This is a lot faster than individually naming each of the ranges.

Now, let’s go back to the Summary sheet for the final steps.
Select all of  Column B (select Column header).
Select Data Validation again.

In the Allow: dialog box select List again
In the Source: dialog box, type =indirect(A1)
(make sure that it is a relative reference - not an absolute cell reference. If it an absolute, every cell will display the same contents).
 
Click OK.
 
 
 
 
Since we are referencing A1 that contains the word Product, Excel is going to give us an error message. When you see it just click Yes that we want to continue.

 
 
 
To get around the error message, you could have selected specific cells in Column B but I took the lazy way of selecting the entire column.
 
 
Click on the drop down arrow at cell B2 and you should see all the model numbers associated with the VCR product line.
Click on A3 and select another product and when you click on cell B3, you will see model numbers that are associated with that product. In my example, I selected CAM for camcorders and B3 shows all the associated model numbers.