DataWright Information Services

Consulting and Resources for Excel and Access

Simplifying cascading validation with complex lists

Validation lists are often used in Excel to improve data entry. By defining a named range and using that as the source for a validation list, you can store your lists in one worksheet and use them throughout the workbook. The problem arises when you want the second list to filter based on your first selection, and so on. You can create named ranges whose names are exactly the same as the items in the parent lists, and then refer to those ranges -- here is a good example from another site showing how to do that --  but there are two major shortcomings.

  • Items with spaces will have those spaces replaced with underscores when you create range names. You will need to work around this to correctly reference the second and subsequent lists.
  • Because each unique item in the cascade needs its own named list, the number of lists can quickly get out of hand and become a maintenance nightmare.

I got caught by the second issue once, and found myself building about 120 lists to maintain a data entry form. So, I went looking for an alternative and built the system described in this article. It is an extension of a tip that I published a while ago, and uses ADO to create the lists on the fly. The result is that you now maintain one large table with all of the possible combinations, which is much simpler to administer.

The sample file contains a table of postcodes in New South Wales, Australia; over 5000 unique entries. As you make a selection, event code on the data entry sheet calls a subroutine to build the dependent list. There is a button on the lookup sheet that rebuilds the first lookup list if you alter the main table. In all, only four lists are used and three of them are maintained for you.

  • You can download the sample file here.

It is an .xls file and has been tested in Excel 2003 and 2007. If you save the file as an .xlsm (for 2007) it will work without modification.

The named ranges

Each of the validated data entry cells has a name; so does the list that each one references. The list names are dynamic so that they don't need to be redefined. You will need one list with a named range for each drop-down.

Triggering the code

General use

The code is triggered from the Worksheet_Change event of the active worksheet. Right-click the worksheet tab, View > Code, and paste this into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Stop the code from firing unless the desired cell has changed
    If Target.Cells.Count > 1 Then Exit Sub
    Select Case Target.Address
        Case Is = Range("BSPName").Address
            Call ADO_Self_Excel("BSPName", "BSP_Name", "Locality", 2)
        Case Is = Range("Locality").Address
            Call ADO_Self_Excel("Locality", "Locality", "Post_Code", 4)
        Case Else
            Exit Sub
    End Select
End Sub

If either range BSPName or Locality updates, the main routine is called. Any other changing cells are ignored.

Updating the first list if the main data table changes

If you alter the main table (in this example, the PostCodes sheet) you will need to re-create the first list in the cascade. There is a button on the Lookups sheet that triggers the following code (in the same module as the main routine) to do the job.

Sub RefreshFirstList()
    Call ADO_Self_Excel("", "BSP_Name", "BSP_Name", 6)
End Sub

ADO_Self_Excel takes four parameters.

  • The first is the named cell corresponding to the validation list. If you enter a name here, the downstream list will be filtered. If you use null quotes ("") the output will not be filtered. This is used to create a set of unique names for the first validation list.
  • The second parameter is the heading of column that will be filtered.
  • The third parameter is usually the adjacent column heading. Values from this column will be transferred to the Lookup sheet.
  • The fourth parameter is the number of the column where the output list will be written.

As an example, Call ADO_Self_Excel("BSPName","BSP_Name","Locality",2) uses the value in the named cell BSPName to filter the column whose heading is BSP_Name and transfer the filtered contents of the Locality column to column 2 of Lookups.

The main routine

The main code goes into a new module, and has two routines. The first, ADO_Self_Excel, does the work of building the lists. The second, RefreshFirstlist, rebuilds the first validation list if the main data table is changed. In the sample file the code is triggered by a button placed on the Lookups sheet. You will need to set a reference to the Microsoft ActiveX Data Objects 2.8 Library to use this code.

The first step is to define the variables and define the file path to the source file. As we are pulling data from a sheet in the same workbook, we just use ActiveWorkbook.FullName as the source path.

Sub ADO_Self_Excel(sCallerRange As String, sSourceCol As String, _
    sDestField As String, iDestCol As Integer)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    Dim sPath As String
    Dim MyConn
    Dim sFilter As String
    
    sPath = ActiveWorkbook.FullName

Next, the filter is defined. You have the option of using a range name for sCallerRange (the normal situation when you build the dependent lists), or a null quote "" (when defining the first list; see the code for RefreshFirstList further down the page). If you use a null quote the filter is made into a null quote, otherwise a wild card character (%) is appended to the range name. 

    'Define the filter and the SQL statement that extracts the names.
    'The validation lists are all on the Menu worksheet; change name to suit.
    'Use % as the wild card character in ADO, not *
    If sCallerRange = "" Then
        sFilter = ""
    Else
        sFilter = (Sheets("Menu").Range(sCallerRange).Value) & "%"
    End If

The SQL that defines the recordset is built next. The WHERE (Criteria) clause is optional, and the code includes it when you pass a range name as the first parameter. By using SELECT DISTINCT we get a unique list; the ORDER BY clause sorts the list.

    'PostCodes is the SOURCE sheet. Change name to suit your setup.
    sSQL = "SELECT DISTINCT " & sDestField & " FROM [PostCodes$]" 
    If sFilter <> "" Then
        sSQL = sSQL & " WHERE " & sSourceCol & " Like '" & sFilter & "'"
    End If
    sSQL = sSQL & " ORDER BY " & sDestField

With the path to the file and the SQL defined, it's time to create the ADO connection and open the recordset.

    'Establish connection to the same file
    'When connecting to Excel instead of a database, you need to define
    'the extended properties as Excel 8.0 (The first Excel version to use ADO)
    
    MyConn = sPath
    
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties").Value = "Excel 8.0"
        .Open MyConn
    End With
    
    'Define a recordset based on the SQL statement
    'The Options parameter is set to adCmdText for a query
    
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, _
      ActiveConnection:=cnn, _
      CursorType:=adOpenForwardOnly, _
      LockType:=adLockOptimistic, _
      Options:=adCmdText

Finally, the list is transferred to its destination. Any existing values in the list are cleared first, then the data is transferred and the recordset and connection are closed.

    Application.ScreenUpdating = False
    
    'Delete existing data on the destination sheet, then
    'transfer the results of the latest filter, starting at Row 2.
    'When done, clean up references to avoid memory leaks.
    
    With Sheets("Lookups") 'Lookups is the DESTINATION sheet. Change to suit.
        .Cells(1, iDestCol).CurrentRegion.Offset(1, 0).Clear
        .Cells(2, iDestCol).CopyFromRecordset rst
    End With
    rst.Close
    cnn.Close
    
    Application.ScreenUpdating = True
    
End Sub

Things to watch

Make sure that your column headings in the main data sheet don't have any spaces. Because we are using ADO they will be regarded as field names, and field names with spaces are best avoided.

You will also need to change the names of the three worksheets -- the positions are flagged in the code. If you don't do this you will get a "Subscript out of range" error at run time.

Wrapping up

Validation lists in Excel are powerful, but if the lists are complex you will find that building and maintaining the dependent lists is hard work. In that case, I hope that you find this article useful.