VBA

Creating Cascading Drop-down Lists in Excel

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 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 you are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments. We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

  • Column A (“ORGANIZATION”) represents the list of valid Organizations that will appear in the Organization drop-down list.
  • Column B (“NAMED RANGE MAPPING”) represents the names given to ranges that will be defined in a later step.
  • Columns D through E (“ORG_X_DEPARTMENTS”) represent the lists of valid Departments that will appear in the Department drop-down list depending upon which Organization is selected.
Excel Cascading Drop-down List Reference Data
Excel Cascading Drop-down List Reference Data

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 ORGS_TO_DEPTS_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B match the names given to the ORG_X_DEPARTMENTS ranges. This value will be used in a subsequent step with an indirect and vlookup data validation formula.

Excel Cascading Drop-down List Reference Data Named Ranges
Excel Cascading Drop-down List Reference Data Named Ranges

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 (“ORGANIZATIONS”) on a new data collection worksheet.

Excel Cascading Drop-down List Top Level Validation
Excel Cascading Drop-down List Top Level Validation

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 ORGS_TO_DEPTS_MAPPINGS named range and returns the name of the Department named range associated with the selected Organization. The indirect function then converts that name, a text value, into the range reference.

Excel Cascading Drop-down List Validation Formula
Excel Cascading Drop-down List Validation Formula

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

Excel Cascading Drop-down List Error Message
Excel Cascading Drop-down List Error Message

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 “Org 1” will cause the validation drop-down list in the Department column to reflect only those Departments associated with “Org 1”. Selecting “Org 2” causes the Department drop-down list to show those Departments associated with “Org 2”.

Excel Cascading Drop-down List Example 1
Excel Cascading Drop-down List Example 1
Excel Cascading Drop-down List Example 2
Excel Cascading Drop-down List Example 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 / dependent relationship. As an example, “Org 1” is selected in the Organization column and “Dept 1” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Org 2”. The Department column retains the value of “Dept 1” which is not valid for the “Org 2” 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 “Dept 1” value would have been removed as soon as the user selected “Org 2”. This code assumes that the parent column is the first column (Column A) in the worksheet and the dependent column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

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

Import Worksheets From One Workbook to Another Using VBA

The procedure ImportWorksheetsFromClosedWorkbook() may be used to copy all worksheets from one workbook (source) into a different workbook (target). The procedure also removes all references to external workbooks. This procedure may be useful if the target workbook has a series of predefined charts or pivot tables that rely on data contained in an external workbook published by another entity.

Option Explicit

Sub ImportWorksheetsFromClosedWorkbook()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim strFilename As String
    Dim strOriginalActiveWsName As String
    Dim bIsFileOpen As Boolean
    Dim objDefinedName As Object
    Dim bEnableEvents As Boolean
    Dim bDisplayAlerts As Boolean
    Dim bScreenUpdating As Boolean
    Dim i As Long
    
    bEnableEvents = Application.EnableEvents
    bDisplayAlerts = Application.DisplayAlerts
    bScreenUpdating = Application.ScreenUpdating
    
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wbTarget = ActiveWorkbook
    strOriginalActiveWsName = wbTarget.ActiveSheet.Name

    strFilename = "c:\test.xlsx"
    
    If Not IsFileExist(strFilename) Then
        MsgBox "File not found: " & strFilename
        Exit Sub
    End If
    
    If IsWbOpen(strFilename) Then
        Set wbSource = Workbooks(strFilename)
        bIsFileOpen = True
    Else
        Set wbSource = Workbooks.Open(strFilename)
        bIsFileOpen = False
    End If

    For i = 1 To wbSource.Sheets.Count
        On Error Resume Next
        If Not wbTarget.Sheets(wbSource.Sheets(i).Name) Is Nothing Then
            wbTarget.Sheets(wbSource.Sheets(i).Name).Delete
        End If
        On Error GoTo 0
    Next

    For Each objDefinedName In wbTarget.Names
        objDefinedName.Delete
    Next objDefinedName

    For i = 1 To wbSource.Sheets.Count
        wbSource.Sheets(i).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
    Next

    If Not bIsFileOpen Then wbSource.Close False

    Call BreakExternalReferences
    Call DeleteExternalNames

    wbTarget.Sheets(strOriginalActiveWsName).Activate

    MsgBox "Source file successfully copied to this workbook."
     
    Application.ScreenUpdating = bScreenUpdating
    Application.DisplayAlerts = bDisplayAlerts
    Application.EnableEvents = bEnableEvents

    Set wbTarget = Nothing
    Set wbSource = Nothing
End Sub

Function IsFileExist(wbName As String) As Boolean
    IsFileExist = Len(Dir(wbName))
End Function

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
    On Error GoTo 0
End Function

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub

Excel VBA Procedures to Break All External References in Workbook

The following Excel VBA procedures may be used to break all external references and links in the active workbook. The BreakExternalReferences() procedure will change all formulas containing references to external workbooks into the resulting value of that formula. The DeleteExternalNames() procedure will delete all defined names that contain external workbook references. Again, any formulas containing references to the defined name will be converted to the resulting value of that formula.

Option Explicit

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub