Macro

If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by only allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.

Step 1 – Define the Reference Data

Let’s assume that we are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments (children). We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

• Column A (“ORGANIZATIONS”) represents the list of valid Organizations to appear in the Organization drop-down list.
• Column B (“ORGANIZATION TO DEPARTMENT MAPPINGS”) represents the names given to ranges that will be defined in a later step.
• Columns D through E (“REF_ORGANIZATION_X_DEPARTMENTS”) represent the lists of valid Departments to appear in the Department drop-down list depending upon which Organization is selected.

Step 2 – Define the Named Ranges

Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B (above) match the names given to the REF_ORGANIZATION_X_DEPARTMENTS ranges. This value is used in a subsequent step using a combination of INDIRECT and VLOOKUP in the data validation formula.

Step 3 – Define the Organization Data Validation (Parent Column)

In this step, we establish the standard data validation on the parent data in Column A (“REF_ORGANIZATIONS”) on a new data collection worksheet.

Step 4 – Define the Department Data Validation (Child / Dependent Column)

Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a VLOOKUP against the REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS named range and returns the name of the Department named range, e.g. REF_ORGANIZATION_X_DEPARTMENTS, associated with the selected Organization. The indirect function then converts that name, a text value, into the named range reference.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))

When you click the OK button, Excel will display the following error message. Click the Yes button to continue.

Step 5 – Test the Cascading Drop-down Lists

Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Organization 1” causes the validation drop-down list in the Department column to reflect only those Departments associated with “Organization 1”. Selecting “Organization 2” causes the Department drop-down list to show those Departments associated with “Organization 2”.

Step 6 – Macro to Validate Parent / Dependent Relationship

Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / child relationship. As an example, “Organization 2” is selected in the Organization column and “Department 6” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Organization 3”. The Department column retains the value of “Department 6” which is not valid for the “Organization 3” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Department 6” value is removed as soon as the user selected “Organization 3”. This code assumes that the parent column is the first column (Column A) in the worksheet and the child column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

Example

Source Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If
exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub