Home > Validation > Dependent > Advanced

Advanced Dependent Drop-Down Lists

Debra Dalgleish - Contextures

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.

short list of top customers

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.

the Dependent Lists with Tables technique

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

short and long customer lists

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.

data validation settings for long or short list

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.

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.

long list with all customers

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

dependent drop down list with IF function

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.

dependent drop down for country and city

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

lists for drop downs

3.3) Simple Dependent Drop Down Formula

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

=INDIRECT(A2)

data validation simple formula

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

dependent drop down for country and city

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.

no country selected

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.

named range World

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.

dependent drop down for world cities

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

dependent drop down for formula with IF

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 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!

dependent drop down block changes to first selection

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:
  5. =IF(C2="", Produce, INDIRECT("FakeRange"))

  6. Click OK
  7. 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.

dependent drop down block changes to first selection

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.

drop down list of Regions

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

existing customer name is 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

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, the INDIRECT function, as usual, to create a dependent drop-down list.

5.2) Dynamic Name Workaround

If you cannot use Named Excel Tables, use the following method for creating dependent lists from formula-based dynamic named ranges:

  1. Create the first named range and drop-down list as described above.
  2. Create the supporting named lists, and name the first cell in each range, e.g., cell B1 is named Fruit and cell C1 is named Vegetables.

    Dependent dynamic lists

  3. Name the column in which each list is located, e.g., column B is named FruitCol and column C is named VegetablesCol
  4. For the second drop down, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first drop-down list is in cell E2:
  • =OFFSET(INDIRECT($E2),0,0, COUNTA(INDIRECT(E2 &"Col")),1)

If two-word items will be used, you can include the SUBSTITUTE function in the formula:

  • =OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0, COUNTA(INDIRECT(SUBSTITUTE($F2, " ","") &"Col")),1)

6) Download Sample Files

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

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

 

 

Last updated: May 18, 2024 1:32 PM