Home > Validation > Dependent > Advanced Advanced Dependent Drop-Down Lists See how to set up advanced dependent drop-down lists in Excel. For example, show a short list or full list, prevent invalid selections, and fix OFFSET list problems. Step-by-step videos, written steps, and Excel files to download.

1) Multiple Item Lists / Multi-Level

Maybe you know how to set up a simple Dependent Drop Down List, but now you need something fancier, such as:

• more than 2 or 3 named ranges for your dependent lists
• multi-level dependent drop downs

For those situations, I recommend using the Dependent Lists with Tables technique. It's easy to set up and maintain, and can support multi-level dependent drop-down lists.

2) Show Long or Short Drop Down List

With this dependent drop down technique, from AlexJ, you can see a full list of customers in a drop down list. Or switch to a short list, with just your top customers. The technique is driven by a formula -- no macros required.

This short video shows the steps, and there are written steps below the video.

2.1) Set Up the Lists

The first step is to create two named lists -- they will be used as the source for the drop-down lists.

1. Type the full list of customers
2. Select all the cells in that list, and name the range: FullList
3. In another column, type "Full List" in row 2.
4. Starting in row 3, type the short list of top customers
5. Select all the cells in that list, including the "Full List" cell
6. Name that range: ShortList

2.2) Create the Drop-Down List

Next, you will create the drop-down list, by using data validation with an IF formula:

1. Select the cell(s) where you want the drop-down list of customers
2. On the Ribbon, click the Data tab, then click Data Validation
3. In the Data Validation dialog box, on the Settings tab, under Allow, select List
4. In the Source box, type an IF formula that refers to the active cell, and the named lists.
   • If you are using the list in a single cell, use an absolute reference
     • =IF($E$3="Full List", FullList, ShortList)
   • For multiple cells, use a relative reference
     • =IF(E3="Full List", FullList, ShortList)
5. Click OK, to close the dialog box.

2.3) Use the Drop-Down List

Select the cell with the data validation list and click the drop-down arrow.

• If the cell does not contain the text "Full List", the drop down will show the short list of top customers.

• If you need to see the full list, select "Full List" from the top of the drop down list
• Then, click the drop-down arrow again.

3) No Selection in Main Drop Down

This is another example of dependent drop-down lists in Excel, with the IF function used with INDIRECT, for a more flexible drop down. In this example:

• If a country is selected in column A, only the cities from that country are in dependent drop down list
• If no country is selected in column A, a list of world cities is shown in the dependent drop-down

3.1) Video: No Selection in Main Drop Down

This video shows the basic set up steps, and then shows how to use the Excel IF function with a dependent drop down. There are written steps below the video, and you can download the sample file below.

3.2) Select Countries and Cities

In this example, there’s a drop down to select a country in column A. If you select USA as the country, cities from the USA appear in the dependent drop down in Column B.

There are city lists in the workbook, and each list is named to match its country name.

• USA: cells C2:C5
• Canada: cells E2:E4

3.3) Simple Dependent Drop Down Formula

Originally, there was a simple formula for the data validation list in column B:

=INDIRECT(A2)

So, if you selected USA in cell A2, dependent drop-down list shows cities from the range named USA.

3.4) No Country Selected

With that simple INDIRECT formula, the drop-down list in column B does NOT work, if no country is selected in column A.

• When you click on the drop down arrow in cell B3, nothing happens.
• Because cell A3 is empty, the INDIRECT formula returns an error, so the drop down does not function.

3.5) Add IF Function to Formula

Instead of a drop-down arrow that does not work, we will change the dependent drop down formula, and add the IF function. The revised formula will give users the option to select a World city, if they have not selected a country in column A.

On the Lists worksheet, there is another range, named World, highlighted in the screen shot below.

3.5.1) Create the Formula

Just as you can use the IF function on the worksheet, you can use it in a data validation formula.

For the dependent drop-down cells in column B, we’ll change the formula to the following:

• =IF(A2="",World,INDIRECT(A2))

After this change to the data validation formula, if you click on a drop down arrow in column B, and no country is selected in column A, the list of world cities appears.

3.6) How the IF Formula Works

Here is what the revised formula does:

• First, the IF function checks the cell in column A, to see if it is blank -- equal to an empty string ""
• IF the cell is blank, the data validation drop down will show the list from the range named World
• Otherwise, show the list named for the country selected in cell A2, using the INDIRECT function

4) Prevent Invalid Selections

After someone selects an item from a dependent drop down, they could go back to the main drop down, and select a different item. That could result in mismatched items in that row.

For example, Fruit could be selected from the main drop down, and then Lemon in the dependent drop down. If Fruit is changed to Vegetable later, the Lemon selection would be incorrect -- it's not a vegetable!

To prevent invalid selections, try one of the following techniques:

• 4.1) Block changes in main drop down (formula)
• 4.2) Clear dependent cell if main drop down is changed (macro)

4.1) Block Changes in First Drop Down

To block changes to the first list, you can change the data validation formula, so the list does not appear unless the second cell is empty. This video shows you how this technique works, and written instructions are below the video.

4.1.1) Block Changes in First Drop Down

With dependent drop down lists, problems can occur, if someone goes back to the first list, and changes it. Then, the first and second selections are mismatched.

For example, in the screen shot below, Fruit is the first choice, and Banana is selected in the second column.

You don't want someone to go back to the first drop down, and choose Vegetable!

4.1.2) How to Block Changes

To block changes to the first list, you can change the data validation formula, so the list does not work unless the second cell is empty.

Instead of just referring to the Produce range for the Produce Type drop down, the formula will check for an entry in the Item column.

• If the Item cell is blank, the Produce Type drop down will show the Produce list
• If an item has been selected, the drop down will try to show the range created by the INDIRECT function -- "FakeRange".
• Because there is no range with that name, the result is an error, and the drop down will not work.

4.1.3) Change the Formula

To change the formula, follow these steps:

1. First, select the data validation cells in the first column
2. On the Ribbon's Data tab, click Data Validation
3. For Allow, select List
4. In the Formula box, type:

=IF(C2="", Produce, INDIRECT("FakeRange"))

5. Click OK
6. If a data validation error appears, "The Source currently evaluates to an error. Do you want to continue?", click the Yes button.

Now, the drop down in the first column (Produce Type) will not work, if the Item has been selected in that row.

4.2) Clear Dependent Cell After Selecting

In the previous section, the data validation formula was designed to prevent selections from the first drop down, if the cell to the right contained data.

Another option is to use a macro, to clear the dependent cell, after making a selecting in the first drop down. That will prevent mismatched selections.

In this example, column B contains a drop down list of Regions. After you select a Region, the drop-down list in column C shows the customers in the selected region.

Go back and select a different region, and the existing customer name will be cleared.

4.2.1) Add Event Code to Clear the Customer Cell

When you select a Region, event code clears the cell to the right. To add the code, right-click the sheet tab, and click View Code.

Paste the following code onto the sheet module. You might need to change the column number, to match the column number on your worksheet.

▶	Note: The sample workbook also has code for clearing multiple dependent cells.

Private Sub Worksheet_Change
			_(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

5) Problem With OFFSET Dynamic Lists

Because the INDIRECT function only works with references, not formulas, the usual method for dependent data validation will not work with lists that use formula-based dynamic named ranges, such as OFFSET ranges.

Instead, use one of the following solutions when a dynamic list is required:

• 5.1) Named Excel Tables (best option)
• 5.2) Dynamic Name Workaround (if you can't use tables)

5.1) Named Excel Tables

To avoid the problem, use named Excel tables, instead of formula-based dynamic ranges.

• Create a table with your list items
• Select all the items in the table column (not the heading)
• Click in the Name Box, type a one-word name the range, and press Enter

Then, use the INDIRECT function, as usual, to create a dependent drop-down list.

5.2) Dynamic Name Workaround

If you can't use Named Excel Tables, use the following method to create dependent lists from formula-based dynamic named ranges,

Main Drop Down

• First, create a list of items for your main drop down list, and name the list.
  • In the screen shot below, the green cells in column A are in a range named Categories, on a sheet named Lists.

• Next, create a normal data validation drop-down list based on that named list, on the Data Entry sheet.

Make Dependent Item Lists

Next, follow the steps below, to create and name the dependent item lists:

• First, in separate columns, type the lists for the dependent drop downs - one list for each category
• In the screen shot below, there are 3 dependent lists - Fruit (yellow), Vegetables (orange) and Other Stuff (blue) lists

• Next, select and name the first cell in each list, using a one-word name. Use the exact names from the Category list, but remove any spaces.
• B1 - Fruit
• C1 - Vegetables
• D1 - OtherStuff   <==No Space

Name the List Columns

Next, to name the column in which each list is located, follow these steps:

• At the top of the column, click on the column button, to select the entire column
• Next, in the Name Box, type the first cell's name, followed by Col (no spaces), then press the Enter key, to complete the name
  • B1 - FruitCol
  • C1 - VegetablesCol
  • D1 - OtherStuffCol

Create the Dependent Drop Downs

Finally, to create the dependent drop down on the data entry sheet, follow the steps below:

• First, select an item from the main drop down, in cell B2
• Next, select the cell for the dependent drop down - cell C2 in this example
• On the Ribbon, click the Data tab, then click Data Validation
• For the Settings, under Allow, select List

• In the Source box, enter the following formula, which refers to the category, in cell B2:
  • =OFFSET(INDIRECT(SUBSTITUTE($B2," ","")),0,0, COUNTA(INDIRECT(SUBSTITUTE($B2, " ","") & "Col")),1)

Note: The SUBSTITUTE function removes the spaces in multi-word category items, such as "Other Stuff", so they'll match the one-word First Cell names and Column names

Get the Excel Files ✅

1. Customers: Download the sample file for the Short or Long Drop Down List of Customers.
2. City/Country: Download the example file for the dependent drop down list with IF - City/Country/World example
3. Block Changes: Download the sample file for the Block Changes to First List example.
4. Clear Dependent: Download the sample file for the Clear Dependent Cell After Selecting
5. Dynamic Lists: Download the sample file for the dependent data validation with Dynamic Lists - Fruit/Other Stuff/Veg example

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Dependent Drop Down Tutorials

Dependent Drop Down Setup Choices

Dependent Lists with Tables

Dependent Drop Down Lists

Dependent Drop Down from Dynamic Arrays

Dependent Drop Downs from a Sorted List

Dependent Lists With INDEX