Clear Dependent Drop Down Cells

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

Here is the list of cities, showing only the options for Canada.

We’ll see how to set this up, and prevent problems, by clearing out the city cell, when necessary

Set up Dependent Drop Down Lists

When one drop down list is based on a selection in another cell, I call that a dependent drop down. I’ve seen other names for the technique, such as “conditional drop down list”. Whatever you call it, it’s fairly easy to set up, as you can see in the video below.

For written instructions, visit one of these pages on my website:

• Create Dependent Drop Down Lists
• Dependent Dropdowns from a Sorted List

  Dependent Lists With INDEX

  Problems with Mismatched Selections

  This technique works nicely, until some troublemaker decides to go back to column B, and select a different country, after selecting a city.

  If they don’t re-select from the city column too, you’ll end up with mismatched countries and cities.

  

  Prevent Mismatches With Data Validation

  To prevent mismatches, you can change the data validation formula in the Country column. Instead of just showing the Country list, the data validation can check column C for an entry first. Then, if there is something in the City column, Excel doesn’t show the Region list.

  For example, use this formula:

  =IF(C2=””,CountryList,INDIRECT(“Nothing”))

  If a City is selected in a row, the Country drop down doesn’t work. You’ll have to clear the City cell, then select a different Country.

  

  Use a Macro to Clear the City Cell

  If you frequently make changes in the first drop down, you might get tired of manually clearing the dependent cell. Life is too short, to be aggravated by little things like that!

  So, if you’re able to use macros in your workbook, you can set up a few lines of code that runs automatically, to clear the city cell, when someone selects a country. When cell B5 is changed in the screen shot below, cell C5 will be cleared.

  

  Code to Clear the Cell

  In the sample code below, the first drop down is in column B, and the code will clear any data in column C, in the same row.

  • The code checks the column number of the cell that was changed, to see if it is in column B (2).
  • If there is a data validation drop down list in that cell, the Validation.Type property is equal to 3
  • In that case, the cell that is 1 column to the right has its contents cleared.

  This code would be pasted onto the sheet’s code module. There are instructions here.

  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 File

  To download the sample file, go to the Dependent Data Validation page, and go to the Download section.

  In the Download section, look for the file named Clear Dependent Cell After Selecting.

  ______________