Dependent Lists - Tables and INDIRECT

In this tutorial, Excel MVP, Roger Govier, shows how to create dependent drop down lists, using named tables and the INDIRECT function


Now I have never been a fan of any of Excel's volatile functions, such as Indirect(), Offset() etc. They are extremely powerful, but in very large worksheets with lots of volatile functions being used, I have known instances where making any data entry or amendment to the sheet can practically "bring Excel to its knees" as the calculation engine kicks in to recalculate all of the dependencies of these volatile functions.

The only way to work sometimes, in these scenarios, is to set Calculation mode to Manual, and after making all of your entries / amendments, press F9 to force the calculation sequence just once.

As a result, my personal preference is to keep away from these volatile functions altogether, and seldom have I not been able to find a way to achieve the same as Offset(), for example, by using a combination of Index() with either Match() or Counta().

However, for small sheets I was playing around recently with Data Validation, using Tables to hold the validation lists, and I found a scenario in which Indirect() can be used to great effect.

Using Tables

The advent of Tables in Excel 2007 as a successor to the List object of Excel 2003, and its subsequent further development in Excel 2010 and 2013, means that where possible, I always use Tables now as opposed to ordinary lists of data. The only exception is where I want my column headings to be dynamic, and based upon a formula, rather than typed text, and this is not allowed with Tables.

With Tables, you can have as many as you want on the same worksheet. This being the case, you can have your data validation entries in a series of single column tables on a sheet, as opposed to having them as separate columns in a single Table.

Single Table

lists in a single table

The Table above has been set up as a single table with a size of $D$7:$R$18, but this will of course adjust if we add more rows and/ or columns. This table I named as tblVal instead of the default name of Table1.

Individual Tables

lists in separate tables

In the second illustration, each column is a table in its own right. You can leave gaps between the columns if you wish, as highlighted with column F, or they can all be contiguous as columns J through S.

As I set up each table, I gave it the same name as the heading in the first row of the table, so the first table is called Region, and it has a size of $E$7:$E$10, the 4th table I named Europe, and is has a size of $J$7:$J$15

Now, the great advantage of having a single column Table, is the number of rows is independent in each table, whereas each column of the combined table has to be of the same length, so that there are blank entries in some columns.

When we have a Data Validation list, we want it to only show the list of valid entries, and not show any blank rows. Using a single table, we need to determine how many rows are used in each column, to define the range that we want to present in our Data Validation list. (I cover this in another article.)

Unfortunately, when Microsoft designed Tables, in my opinion they missed out on one great design feature, as you cannot use just the column header to refer to a range for a Data validation list. Within tblVal, you cannot just refer directly to Europe, for example, to get the list of entries from J7:J15.

You can return that range however, if you use Indirect, by typing =INDIRECT("tblval[Europe]")

Alternatively, you need to create a named range with Name Manager of Europe with Refers to: =tblval[Europe]

Maybe this will get addressed in future versions of Excel.

But in this article I want to concentrate on using several single column tables as our source data, rather than the single table multiple column approach (as discussed in this article)

Single Column Tables

With single column tables, we have already discussed the fact that each will have its own dimensions (always just one column) but a variable number of rows.

But there is another great advantage, and whether it was by design, or is just a "quirk" of Tables, and that is, if you name the Table the same as the text in the header cell on the tables column, you can refer to this directly and return the range of cells belonging to that name. You can do this without having to create a named range, and without having to refer to =INDIRECT(tablename[Header])

With the single column table =Europe will return exactly the same range as =INDIRECT("Europe[Europe]")

So using a single column table gets us away from one level of using Indirect, allows us to use directly our column header text to refer to the range and gives us a dynamic range which will only ever include valid entries without any blank cells (unless we enter a blank cell in a table deliberately).

We can click on the column header and Sort it ascending or descending at any time, and it will sort that data validation list quite independently of any other list.

Use Tables in Data Validation

Let's look at how to use these tables in data Validation - especially dependent data validation.

Let's deal first with the case where our data entry is on a sheet in ordinary list (as opposed to a table). For example. On a sheet called Data Entry we might have it set up with headings like shown below, with data validation applied to the cells where we are going to enter data. In this example, I have started at A1 of the data entry sheet, it could start anywhere on the sheet but you would need to adjust the formulas which I will show shortly to account for this.

data entry cells

Now we have to use the Indirect function, not to create our data validation list, as we have already achieved that as shown above, but to provide a way for having the data validation list that is brought up in any cell, be dependent upon previous entries in that row.

Data Validation has been applied to the cells in column A with the formula =INDIRECT($A$1) note that this is an Absolute reference, so it will always be referring to cell A1 In other words, cells in this column will be the list which is the same as the text that is held in A1 - Region. We could just as easily have used =Region in this case, and not used the Indirect method, but the method shown will allow for whatever you choose to have as the heading in the first column of your workbook.

In the other cells, it will always have to use the Indirect function, taking as its argument, the value that has already been entered in the previous cell in that row. So for all other cells in this example, the data validation will have been set up by selecting the whole range of cells and then setting the validation to be List and =INDIRECT(A2)

As the reference here is relative, then it will adjust automatically, so that when the cursor was in say cell D2, it would be referring to =INDIRECT(C2) and give the validation list for the name England.

If your data validation is on a different part of the sheet, not starting in the first column nor the first row, then just ensure that you set this relative Indirect formula to always be referencing the cell immediately below your first heading.

Even if you are using a Table for your Data Entry, because you have to use Indirect for this method to work, there is little point in bothering with structured referencing, and you can use the same formula with your table.

In the example below, the data entry table tblData is located at J8:M12 and the Data Validation entry in column J has been set at =INDIRECT($J$8) and for columns K, L, and M is has been set as =INDIRECT(SUBSTITUTE(J9," ","_"))

data entry table tblData

Do not use spaces in Names

Excel does not permit the use of spaces in Named ranges, nor in Table names, so you need to be aware of this when setting up your Data Validation tables.

For example, in the list for MEA, there is Turkey, South Africa, Jordan, Egypt and Israel. When you set up the table for South Africa, you would need to insert an underscore in place of the space and call it South_Africa, and in the header row for that table it would need to be South_Africa. Even if you have inserted the underscore in the table name, but leave the space in the header, Excel will not return the range just using the text of the header.

So, in case there are spaces in any of your list data e.g. British Columbia under the list for Canada, we can ensure that Excel uses the underscore to correspond with the names you have used in the validation table by using the Substitute function.

If cell A2 contained British Columbia, then =SUBSTITUTE(A2," ","_") would return British_Columbia The same formula if cell A2 contained Ontario, would still just return Ontario - as there are no spaces to Substitute.

It is better therefore to change our validation formula

from =INDIRECT(A2)


VBA to Create Single Column Tables

If you already have a sheet with lots of data already entered in standard form, and you don't want to manually create each of the single column tables that you require for this method, then the code as shown below will create all the tables for you, and will ensure that any spaces are removed when setting up Table names and Header entries in the first row of tables created.

This code assumes that the only thing on your sheet are the entries you want to use for Data Validation. It uses the UsedRange object to determine where body of data exists on the sheet that you want to convert into single column Tables, and that all header items are in the same row.

Sub CreateTables()
Dim firstCol As Long, lastCol As Long
Dim firstRow As Long, lastRow As Long
Dim c As Long, x As Long
Dim TableExist As String
Dim TableName As String
Dim shRange As Range
Dim ws As Worksheet
' change this if required to name of your sheet
Set Ws = Sheets("Lists")
Set shRange = ws.UsedRange
firstCol = shRange.Column
firstRow = shRange.Row
lastCol = Cells(firstRow, Columns.Count) _

With Ws
   For c = firstCol To lastCol
     If Cells(firstRow, c) <> "" Then
      lastRow = Cells(Rows.Count, c).End(xlUp).Row
      x = x + 1
      On Error Resume Next
      TableExist = Cells(firstRow, c).ListObject.Name
      On Error GoTo 0
      .Cells(firstRow, c) = _
         WorksheetFunction.Substitute _
         (.Cells(firstRow, c), " ", "_")
      TableName = .Cells(firstRow, c).Value

      If TableExist <> TableName Then
          ws.ListObjects.Add(xlSrcRange, _
            Range(Cells(firstRow, c), _
            Cells(lastRow, c)), , _
             xlYes).Name = TableName
      End If
     End If
   Next c
End With
On Error GoTo 0
End Sub

If your validation data is not the only thing on the sheet, then as long as your data is in a contiguous block AND has blank cells surrounding the region of this data, then you can modify the code above, changing the line

Set shRange = ws.UsedRange 


Set shRange=ws.Range("E5").CurrentRegion 

Replacing Range("E5") with whatever is the cell location for the first row and column of the data you want to be converted.

Clearing Cell Entries

Since Data Validation is all about ensuring that the user only enters valid data into cells, the "wheels can come off the wagon" if the user goes through the Selection process and chooses, for example Americas > Canada > Ontario > Toronto and then goes back and changes Americas to Europe. Clearly, the values following Americas will all be incorrect, unless the user does go and make each of the changes.

If you can accept a little VBA in your workbook, then the .xlsm version as shown below includes the following piece of event code to clear entries on the same row, to the right of the changed value. As there may well be further data in the Table which is numeric data and should not alter even if the prior values change, we need to inform the code how far it should clear the data.

I have used a Variable called maxcols in the code below, which has been set to 6 for this example, but you will need to alter dependent upon your own situation. The code below has been copied onto sheet Data Entry

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tr As Long, tc As Long, maxCols As Long
On Error GoTo exit_sub
tr = Target.Row: tc = Target.Column

'//// set the column number for the last column
'//// you want to be cleared automatically
'//// when an upstream cell is changed
'//// Often you will have other
'//// numeric data in a table which
'//// does not need to be cleared
'//// if other values are amended

maxCols = 6

'//// In this example, there are dependent validations
'//// in columnc to F so maxcols has been set to 6

If Target.Count > 1 Then GoTo exit_sub
If Not Intersect(Target, _
      Me.ListObjects(1).Range) Is Nothing Then

'/// Assumes there is a single Table on this sheet,
'///   hence ListObjects(1) can be used,
'///  it does not have to be named.
'/// You can be specific and use the form
'/// If Not Intersect(Target, _
'///   Me.ListObjects("tblData").Range) Is Nothing Then

    If tc = maxCols Then GoTo exit_sub

    Application.EnableEvents = False
    If tc < maxCols Then
        Range(Cells(tr, tc + 1), _
          Cells(tr, maxCols)).ClearContents
    End If
'////  This next section checks whether
'////  the cell has had an entry made,
'////  and if so uses Sendkeys to automatically
'//// show the drop down list of possible entries,
'//// without having to click the dropdown arrow.

If Target <> vbNullString Then
        Target.Offset(0, 1).Select
        If ActiveCell.Validation.Type = 3 Then
            Application.SendKeys ("%{DOWN}")
        End If
    End If
End If
    Application.EnableEvents = True

End Sub

In addition to clearing entries ready for new values to be selected, the code checks to see if a new value has been entered into the cell (as opposed to just a deletion of value) and if so, it selects the cell one column to the right.

A check is then made to see if this newly selected cell contains Data Validation, and if so it produces the list of appropriate dropdown values ready for the user to select, without the need for them to click the arrow in the cell first to produce the list. This can speed up data entry considerably.

To use this version, download the sample file WITH macros, below.

Download the Sample File

Download one of the following zipped sample workbooks --

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at:
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191

Roger Govier

Related Tutorials

Data Validation Basics


Excel Tables

Create Dependent Lists

Dependent Dropdowns from Sorted List

Dependent Lists INDEX

Dependent Lists Tables

Sample Files - Roger Govier

Search Contextures Sites





30 Excel Functions in 30 Days


Excel Data Entry Popup List





Excel UserForms for Data Entry


Learn how to create Excel dashboards.

Last updated: March 28, 2016 3:18 PM