Contextures

Create Dependent Drop Down Lists

Limit the choices in an Excel drop down list, depending on the value in another cell. For example, select "Fruit" in cell B2, and "apple, banana, peach" appear in the drop down list in cell C2. Watch the video below, to see how it works.

Dependent drop-down

Dependent Drop Down Lists

In this dependent data validation example, if a country is selected from the first drop down, only that country's cities appear in the next column's drop down list. The written instructions are below the video.

After you learn the basics, you can use the Excel IF function to make the selection more flexible. If no country is selected, the City column will show a list of world cities in the dependent drop down list.

Getting Started

This example will have a workbook with two sheets -- a data entry sheet, and a sheet with lists.

  1. Create a new workbook
  2. Change the name for Sheet1 to "DataEntry"
  3. Insert a new sheet in the workbook, and name that sheet "Lists"

new workbook with two sheets

Create Named Lists

Follow the steps below, to create Named Lists. These lists will contain the items for the dependent data validation drop down lists. In this example, there will be 3 lists:

  • Produce types
  • Fruit names
  • Vegetable names

1. Create the first Named List

In this example, the first list will be named Produce. It will have a list of the Produce categories -- Fruit and Vegetable.

Type the List

The main list will have one-word items in it (Fruit, Vegetable). Later, those words will be used a range names. If you need to use multiple-word entries, see: Using Two Word Items

  1. On the Lists sheet, in cell B2, type the heading, Produce List
  2. In cells B3 and B4, type Fruit and Vegetable.

Create a Table

To create a named table for your list, follow these steps. This makes your list dynamic, so new items will automatically appear in your drop down lists.

  1. Select a cell in the list, and on the Home tab, click Format as Table
  2. Click on one of the Table Styles
  3. Add a check mark for My table has headers
  4. Click OK

    named table

Create a Named Range

Next, follow these steps to create a named range. You will use this name later, when making the drop down list.

  1. Select the items in the list, but not the heading. In this example, cells B3:B4 are selected
  2. Click in the Name box, to the left of the formula bar
  3. Type a one-word name for the list -- Produce
  4. Press the Enter key, to complete the name.

First named range

2. Create More Named Lists

Next, follow the steps to create a separate table for each produce type -- one with a list of fruit names, and one with a list of vegetable names.

These lists will be named, and the names will be an exact match for the entries in the Produce list.

  1. On the Lists sheet, in cell D2, type Fruit List
  2. In cells D3:D6, type the fruit names -- Apple, Banana, Lemon, Peach.
  3. Format the list as a named Excel table
  4. Select the fruit names in cells D3:D6
  5. Click in the Name box, to the left of the formula bar
  6. Type a one-word name for the list -- Fruit.
  7. Press the Enter key, to complete the name.
  8. Second named range

  9. Repeat these steps to create another list with the Vegetable names -- Cabbage, Lettuce, Rutabaga, and name that list as Vegetable.

Third named range

Add the Drop Down Lists

Next, data validation will be applied on the DataEntry sheet

  • Produce Type column will have a simple list
  • Item column will use a formula to show items for the selected product type

1. Start the Data Entry Sheet

First, you'll enter the headings on the data entry sheet, and set up a named table.

  1. On the DataEntry sheet, type the headings in B2:C2 -- ProductType and Item
  2. Select cell B2, and and on the Home tab, click Format as Table
  3. Click on one of the Table Styles
  4. Add a check mark for My table has headers
  5. Click OK

start data entry sheet

2. Create the First Drop Down

Next, you'll create a drop down list in the Produce Type column

  1. On the DataEntry sheet, select cell B3
  2. On the Ribbon, click the Data tab, then click Data Validation.
  3. From the Allow drop-down list, choose List
  4. In the Source box, type an equal sign and the list name: =Produce
  5. start data entry sheet

  6. Click OK, to complete the data validation setup.
  7. To test the drop down, click the arrow, and select Fruit from the list

select fruit from the drop down list

2. Create the Dependent Drop Down

Next, you'll create a dependent drop down list in the Item column. This cell will have a formula to create the data validation drop down list. Read more about the INDIRECT function here.

  1. On the DataEntry sheet, select cell C3
  2. On the Ribbon, click the Data tab, then click Data Validation..
  3. From the Allow drop-down list, choose List
  4. In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Produce Type column:
    In this example, cell C3 is active, so the formula will refer to the Product Type cell in row 3: =INDIRECT(B3)
  5. Click OK.

INDIRECT formula for dependent drop down list

Note: If cell B3 is empty, you'll see the message shown below. Click Yes to continue.

Source error message

To test the dependent drop down list, click the arrow, and select one of the fruit items from the list

test the dependent drop down list

Test the Drop Down Lists

To test the dependent drop down list again, follow these steps

  1. Select cell C3, and press the Tab key, to start a new row in the data entry table.
  2. In the Produce Type column, select either Fruit or Vegetable, from the Produce drop down list.
  3. Press the Tab key on your keyboard, to move to the Item column, in the same row.
  4. In the Item column, click the drop down arrow, and select an item. The drop down list shows either Fruit or Vegetable items, depending which has been selected in the Produce Type column

test the dependent drop down list

NOTE: If a Produce Type has not been selected, the Item drop down in that row will not work.

Adding a Third Dependent List

If you need to, you can add another set of dependent data validation dropdown lists that depend on the selections in the first two dropdowns. For example, select a country and region, then select a city in that region.

city drop down list

  1. Create the two named ranges and dropdown lists as described above.
  2. Create another set of named ranges, naming them for the available combinations in the first two Excel data validation drop-downs. For example, you might create ranges named CanadaOntario and USANewYork.

    named ranges

  3. For the third dropdown, choose to Allow: List, and use a formula that combines the entries in the first two columns, and removes the spaces from the names. For example, in cell D2, the data validation formula would be:

  =INDIRECT(SUBSTITUTE(B2&C2," ",""))

Using Two-Word Items

In some workbooks, you might need to have two-word items in the first Excel data validation drop-down list. For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit'

  1. Create the first named range and dropdown list as described above.
  2. Create the supporting named lists, using one-word names, e.g. RedFruit, GreenFruit, YellowFruit
  3. For the second dropdown, choose to Allow: List, and use a formula that removes the spaces from the names. For example:

  =INDIRECT(SUBSTITUTE(A2," ",""))

Dependent two word lists

Using Items with Illegal Characters

You may need items in the first Excel data validation drop-down list that contain characters not allowed in range names, such as the ampersand (&).

dependent drop down illegal 3rd

For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow & Orange Fruit'. For the dependent lists, you can create ranges with one-word names, such as YOFruit. Then, create a lookup table, which lists each item in the first Excel data validation drop-down list, and the range where its dependent items will be stored.

To start, create the item lists and the first Excel data validation drop-down:

  1. Create the first named range and drop-down list as described above. In this example, the range is named ProductList, with values in cells A6:A8. The drop-down list in cell A2 uses ProductList as its source.
  2. Create the dependent lists, and name them, using one-word names, with "List" at the end, e.g. RedFruitList, GreenFruitList, YOFruitList. In this example, RedFruitList is in A11:A12, YOFruitList is in A15:A16 and GreenFruitList is in A19:20.
  3. Select an item from the drop-down list in cell A2.

Next, you'll create the lookup table, to match each item with its dependent items' range name.

  1. In the column to the right of the FruitList range, enter the code name for each item's dependent list. For example, YOFruit is entered as the code name for Yellow & Orange Fruit.
  2. Name the lookup table. In this example, the range A6:B8 is named ProductLookup.
  3. Select cell B2, and from the Data menu, choose Validation.
  4. Choose to Allow: List.
  5. For the Source, enter a formula that uses a VLookup formula to find the dependent list's range name -- a combination of the code name and "List".
    For example:   =INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")

With Red Fruit selected in cell A2, the VLookup formula will return RedFruitList as the range name for the dependent list. The RedFruitList items will be displayed in cell B2's drop-down.

dependent drop down illegal 3rd

To create a 3rd dependent drop down list, use the same techniques, to create lookup tables and items lists.

dependent drop down illegal 3rd

In the screen shot below, there are lookup tables named RedFruitLookup, YOFruitLookup and GreenFruitLookup. Items lists, with the code names and "List", have been added to the worksheet.

dependent drop down illegal 3rd

In the data validation window, use this formula for the 3rd drop down:

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

The formula finds the lookup table based on the product type selected in cell A2, e.g. RedFruitLook, and gets the code for the selected product -- Mac. It adds "List" to the code, and shows the items in the MacList range.

items from MacList

Using Dynamic Lists

Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with lists that use formula-based dynamic named ranges, such as OFFSET ranges. Use one of the following solutions when a dynamic list is required:

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 one of the INDIRECT function examples shown above, to create a dependent drop down list.

Dynamic Name Workaround

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

  1. Create the first named range and dropdown 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 dropdown, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first dropdown 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)

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.

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, Vegetable is the first choice, and Banana is selected in the second column.

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

dependent drop down 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.

To change the formula:

  1. 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

Now, the drop down in the first column won't work if the Item has been selected in that row.

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

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

Download the Sample Files

  1. Download a sample file for dependent data validation Intro - Fruit/Vegetable example
  2. Download a sample file for dependent data validation Advanced - 3rd list; 2-word/illegal
  3. Download a sample file for dependent data validation with Dynamic Lists
  4. Download a sample file for Clear Dependent Cell After Selecting

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

More Tutorials

Data Validation Basics

Dependent Dropdowns from a Sorted List

Dependent Lists With INDEX

Hide Previously Used Items in a Dropdown List

Use a Data Validation List from Another Workbook

Data Validation Criteria Examples

Data Validation Tips

Data Validation With Combo Box

Search

Search Contextures Sites

 

Custom Search

 

Excel Data Entry Popup List

 

 

Get weekly Excel tips from Debra

 

 

Excel Tools add-in

 

 

Excel Data Entry Popup List

 

 

 

Update Your Excel Skills

 

Excel Data Entry Popup List

 

 

 

 Get Excel News

Last updated: September 19, 2017 3:52 PM