The INDIRECT function is a powerful function as it allows you to carry out different tasks that come in very handy. In this guide, we will discuss how to make use of the INDIRECT function to create ‘Dependent Dropdown Lists’. An overview of how to do that is discussed in the paragraphs below.

Step 1:

In the first step, you need to order the data in different columns like ‘Category’, ‘Breakfast’, ‘Beverage’, ‘Meat’, etc. keep in mind that the column named ‘Category’ contains different values including Meat, Beverage and Breakfast.

Step 2:

In the second step, you need to assign different Named Ranges to your columns. In order to do that, you need to access the Name Box which is present at the left of the Formula Bar. Then you need to highlight the values of Category and then type within the Name Box.

Step 3:

In the third step, it is required to carry out the same steps for the rest of the columns. You can do that by typing the following commands:

Meat column values – Meat (Named Range) and likewise for the other columns.

Once you are done with creating the Named Ranges, you need to select Name Box Dropdown in order to see the new Named Ranges.

Step 4:

Now you need to start off with creating your dropdown lists. Select the cell in the table that you need to keep your first dropdown list.

Step 5:

In the fifth step, go to ‘Allow’ and select ‘List’ in the dropdown list. After accessing the source area, you need to type ‘=Category’. This is important for accessing the Named Range of Category that was defined in the second step.

Step 6:

Now you need to select a cell where you could place your dependent dropdown list.

Step 7:

Suppose you have selected the cell H10, now you need to write:

=INDIRECT($H$10)

Pressing enter would return the values of the Named Range from your dropdown list that was selected for the cell H10.