Home > Validation > Dependent Drop Downs > Index Tables Create Dependent Lists using Tables In this tutorial, Excel MVP, Roger Govier, shows how to create dependent drop down lists, using named tables. See how to set up the named ranges, and use those range names in data validation formulas.

Introduction

This video shows how to set up multiple dependent drop down lists, based on columns in Excel Tables. The written instructions are below the video. Go to the Sample Files section, to get the completed Excel file.

Background

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.

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.

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.

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

After the other tables are set up, data validation drop downs will be added to this table.

These will be dependent drop down list, with each list showing the items based on your selection in the previous column.

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.

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

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.

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

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.

The named range _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.

Main Table - Data Validation Drop Downs

The final step is to set up Data Validation drop down lists in the main table.

• The first column, Regions, is not dependent, and shows a list of all the regions, from the named range _Regions.
• The other 3 columns -- Country, Area and City -- will have dependent drop downs, based on the named range _UseList.

Regions Column Drop Downs

To set up the drop down lists in the first column (Regions), follow these steps:

• First, select all the data cells in the Regions column
• Next, on the Excel Ribbon's Data tab, in the Data Tools group, click the Data Validation button
• When the Data Validation dialog box open, go to the Settings tab
• First, click in the Allow box
• Then, from the Allow drop down list, select List
• Next, click in the Source box, and type this formula:
  • =_Regions
• Click OK to close the Data Validation dialog box

Other Columns Drop Downs

To set up the drop down lists in the remaining 3 columns, follow these steps:

• First, select all the data cells in the Country, Area and City columns
• Next, on the Excel Ribbon's Data tab, in the Data Tools group, click the Data Validation button
• When the Data Validation dialog box open, go to the Settings tab
• First, click in the Allow box
• Then, from the Allow drop down list, select List
• Next, click in the Source box, and type this formula:
  • =_UseList
• Click OK to close the Data Validation dialog box

Test Drop Down Lists

After you set up the data validation drop down lists, test them, to make sure they're working correctly.

• First, select a region name in the first column
• Next, select a country name in the second column
• In the Area column, you can select an area from that country
  • Note: If the Area drop down doesn't work, no areas are in the values table for that country.
• If you were able to select an Area, try the City drop down, and select a city from that area
  • Note: If the City drop down doesn't work, no cities are in the values table for that area.

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

NOTE: This macro uses the SendKeys method

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.

Get the Sample File

Get one of the following zipped sample workbooks --

• Dependent Lists With Tables -- NO Macros
• Dependent Lists With Tables -- WITH Macros

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

Related Tutorials

Data Validation Basics

Dependent Drop Down Setup Choices

Dependent Drop Dows in Tables

Create Dependent Lists

Dependent Dropdowns from a Sorted List

Dependent Lists With INDEX

Data Validation Criteria Examples

Data Validation Tips