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

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

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

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

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

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
  • 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
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

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 = ""
        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, _

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