Related Drop-down lists

Creating related drop down lists make your forms and sheet more effective.

Create Drop down list go to the Data tab, in the Data Tools section select Data Validation.

In this example, the first drop-down will be the type of pet.

Once you have selected the type of pet, you will select the breed of the pet that you selected.

From the dialogue box presented select List,

Enter the items in Source, that you would like to have in your list separated by commas.

We have entered dog, cat, horse.

These are the categories of the animals.

We will create named ranges for each category in our list.

For Dog, we will enter three types of dogs and select these cells then go to the Name Box and name the range dog.
Press Enter

Create named ranges for the other two categories.
For the second drop-down list uses the indirect function to access the names in the categories that we have in our named ranges.

To create a relate drop-down go to another cell and go to Data Validation

Select List and in the source line you will enter

=indirect(the cell where the original drop down)

Click OK and you will have are related drop-down list.

video on Related Drop-Down Lists

Check out my Excel online Courses