Contextures

Create Dependent Lists using Tables

In this tutorial, Excel MVP, Roger Govier, shows how to create dependent drop down lists, using named tables.

Introduction

Previously, I wrote about a method of to create Dependent Data validation lists using Index, as an alternative to using volatile functions like INDIRECT or OFFSET. This was done when we were using Excel 2003, where you used named ranges to create Dynamic Ranges, and where the List object had just been introduced.

This still works and there have been a few minor tweaks along the way, to deal with queries that users have written, where they have a slight difference in their requirements.

It has long been overdue for me to revisit this area and show some better and more effective ways using the TABLE object which was introduced with Excel 2007 and has been slightly modified since for 2010 and 2013.

In this example, 2 tables are created,

  • one to hold the List of items to be used as Data Validation Headings,
  • the second to be used as the table for Data Entry.

Along with these 2 Tables, there are just 3 named ranges set up with Name Manager, which then permit any number of dependent data validation drop downs to be used in the Data Entry table.

How to apply Data Validation to cells has been well described in other sections of this site, so that will not be covered here. It you need instructions, please visit the Data Validation Basics page.

Setting up the Tables

In the sample workbook, the Data Entry table has been created on a sheet which itself has been named Data Entry - but it could have any name. First, the headings are typed on the sheet.

type headings on sheet

Next, a Table was created using the Table command on the Ribbon's Insert tab.

When the tables are created, you can choose whatever Formatting you want for the table from the Table Styles menu. In this case the Style chosen was Table Style Medium 2.

format the table

I also chose to give this table a name in place of the default Table1 and I named it tblData

change the table name

Create the Second Table

In exactly the same way a list of the data required for the Data Validation was typed on another sheet (in reality it could be on the same sheet if required) and this was converted into a Table by the same method and this was given the name tblVal.

create the second table

Setting up the Named Ranges

There are 3 named ranges used in this method, and the setup for each range is described below.

1. _Regions

This is the range that will be allocated as the data validation list for the column Regions in tblData. This is a fixed list, in that it relates only to the first column of data entry. The other named ranges will help to derive the single range that will be used for every other column on the data entry table, as each of these will be dependent upon the preceding column to the left.

For this range, I have used structured table referencing to refer to just the cells in the first column of the tblVal data, and only for those cells within this column that contain data. Using Formulas > Name Manager > New, I created the formula in the Refers To box as

=INDEX(tblVal[Regions],1,1):INDEX(tblVal[Regions],COUNTA(tblVal[Regions]))

name the first range

With structured referencing, the reference tblVal[Regions] will return the column of data called Regions in the Table called tblVal. In this case it will return the range $D$8:$D$18. The great thing about tables is, although the returned range looks like an absolute range, it is in fact dynamic, and will grow if more data rows are added (and shrink if rows are deleted from the table.

However, this range, only has entries in cells D8:D11, and cells D12:D18 are empty. For our data validation, we only want a list of the cells with data so we need to adjust the range that _Regions returns to account for this. We do this with the INDEX function.

In the Refers to formula:

  • =INDEX(tblVal[Regions],1,1) will return the first cell within the range i.e. D8
  • =INDEX(tblVal[Regions],COUNTA(tblVal[Regions])) will return the cell represented by what is returned from the COUNTA function on range D8:D18 i.e. D11

As a result, the named range _Regions refers to D8:D11 and when used with data validation will show only the values that are available for selection, as in the screen shot below.

drop down list with available items

2. _MainList

This is the range which will identify which column of data from the table tblVal needs to be used as a dependency of the item in the cell to the left in the table tblData. Let's look at a small section of our tblData

look at tblData

So, if we have selected Europe as the Region, we want to be shown a drop down list in column D under Countries of the countries that fall within Europe in the data validation table.

The formula which gives this range is

=INDEX(tblVal,0,MATCH(INDEX(tblData[@],COLUMN()-COLUMN(tblData)),tblVal[#Headers],0))

Let's dissect this to understand what it is doing. Index takes 2 parameters, firstly a row coordinate followed by a column coordinate.

=INDEX(tblVal,0

So, we are wanting to Index tblVal (which has the range $D$7:$R$18 on sheet Lists) and we have given it the first coordinate of 0. When the row coordinate is 0, index returns the whole column for the value provided by the column coordinate, so the result of this formula is going to be a whole column of values.

The column coordinate we are passing to Index, is a complicated Match function. What we want to find is the column name which has the same value as in the cell to the left of where our data validation formula is going to act. In this example, our data validation formula is going to be located in column D, and our dependent list will be based upon the value immediately to the left in column C, which is Europe

Finding the item we want to Match (Europe) is achieved by :-

INDEX(tblData[@],COLUMN()-COLUMN(tblData))

  • tblData[@] returns the whole row of data at the current row position - which will be $C$7:$F$7
  • COLUMN() returns the column number as is normal for this functions use anywhere within Excel, and as we are in column D, it will return 4
  • COLUMN(tblData) is part of structured referencing and will always return the column number of the first column in a Table, and for this table it will return the value of 3

So COLUMN()-COLUMN(tblData) will give us the relative position of the column and will return a value of 4 -3 = 1

Overall, then

INDEX(tblData[@],COLUMN()-COLUMN(tblData))

Will be equivalent to =INDEX($C$7:$F$7,1) which will return the value Europe

So now, we have the value to use in our Match function, and where we are trying to match this value is in the header row of tblData In structured referencing,

tblVal[#Headers] returns the range of the header row of the table, which in this case would return the range Lists!$D$7:$R$7

[Note: whilst the construct Column(tblData) returns the column number of the first column of a table, the construct Row(tbldata) returns the row number of the first row of the Datatbody of the table, in this case 8, and not the Header row, which is row 7 ]

  • MATCH(INDEX(tblData[@],COLUMN()-COLUMN(tblData)),tblVal[#Headers],0) therefore becomes
    • MATCH("Europe",tblVal[#Headers],0) the trailing 0 meaning we want to find an exact match, even if our data along the header row is not in a sorted order, and this then becomes effectively
      • MATCH("Europe",Lists!$D$7:$R$7,0) which returns the result of 4, as Europe is the 4th item in that range.

Our original _Mainlist formula thus is effectively INDEX(Lists!$D$7:$R$18,0,4) which as the first parameter is 0, will return us the whole of the column which is Lists!$G$7:$G$18

Before we move on to the next named range, take another look at the information above, and evaluate things as if you were in cell F6 of the small table above.

Stepping through the evaluation process here, you will see that

  • COLUMN()-COLUMN(tblData) will return a value of 6 -3 = 3 and
    • INDEX(tblData[@],COLUMN()-COLUMN(tblData)) will be equivalent to =INDEX($C$6:$F$6,3)
      • which will return the value Ontario

So this construct enables us to use structured referencing to give us the equivalent of the Volatile OFFSET function, and return a value which is one to the left =OFFSET($F$6,0,-1) which would also return Ontario

3. _UseList

Our final named formula is the one that will be used for all data validation entries in our table tblData, apart from the first column which will use _Regions.

We have seen how _Mainlist returns us the column of data that we want from our validation table tblVal. We have also seen that it returns us the complete column within the table, whether it contains data or not. In the example above, the return was Lists!$G$7:$G$18, but if we look at the table, we can see that cells G16:G18 are blank cells. Since one of the main purposes of Data Validation is to ensure that users can only enter data that is valid, we do not want to offer them blank cells as part of their selection.

_Uselist, takes the output from _MainList and returns a set of data that are just the cells that contain data. =INDEX(_MainList,1,1):INDEX(_MainList,COUNTA(_MainList)) It is just like the formula used for Regions by using =INDEX(_MainList,1,1) to give the first entry from the _MainList, and using INDEX(_MainList,COUNTA(_MainList) to provide the last cell reference that contains data.

So now we have a method of having any number of Dependent Data Validations using just three named formulae and Excel's Tables. The big advantage with Tables is they are automatically dynamic and will grow or shrink as we add more data to any of the rows. Additionally, further dependencies can be added by adding more columns with a heading equivalent to one of the cell values in a prior dependency list, and there is nothing to add or alter to the formulae we have created.

The other tremendous advantage with Tables, is that they can be moved around at will anywhere within the workbook, without breaking any of the formulae. You don't have to ensure that you are in any specific cell within your data entry table when setting up the formulae to make it all work.

A workbook showing all of these features may be downloaded at the end of this page. To use this version, download the sample file with NO macros, below.

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 drop down 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
exit_sub:
    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 drop down 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:

roger@technology4u.co.uk
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191

Roger Govier Roger Govier

Tech4U logo

Related Tutorials

Data Validation Basics

Create Dependent Lists

Dependent Dropdowns from a Sorted List

Dependent Lists With INDEX

Data Validation Criteria Examples

Data Validation Tips

Search Contextures Sites

 

pivot power premium

 

 

pivot power premium

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

Excel charting tool

 

Excel UserForms for Data Entry

 

Last updated: December 4, 2016 3:30 PM