Excel data validation list from table indirect

Author: Oscar Cronquist Article last updated on October 15, 2019

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are two approaches, the INDIRECT function or a named range.

The INDIRECT function is a volatile function meaning it recalculates every time Excel recalculates which is not the case with most Excel functions. If used a lot the INDIRECT function may slow down your workbook calculations considerably, be careful.

It is enough to calculate a formula once, there is no need to calculate all cells every time Excel recalculates. This makes your worksheets faster and more efficient, however, this is not the case with a handful of functions and the INDIRECT function is one of them.

Another downside with the INDIRECT function apart from being volatile is that the Excel Table name is "hardcoded" into the formula. The formula will stop working if you change the Excel Table name.

Table of Contents

The image above shows a drop-down list populated with Excel Table header values, this formula allows you to use Excel table headers as values in a drop-down list.

=INDIRECT("Table1[#Headers]")

You can also create a named range and reference the headers there.

1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on "Name Manager" button to open the "Name Manager" dialog box.
   
3. Press with left mouse button on the "New" button.
4. Type the reference, in this case: =Table1[#Headers]
   
5. Press with left mouse button on OK button.
6. Press with left mouse button on Close button.

Now use the named range name Headers in the Data Validation dialog box.

Watch this video to learn more

How to populate a drop-down list with values from an Excel Table column?

The easiest way is to use the INDIRECT function to create a reference to an Excel Table column. References pointing to an Excel Table are called "structured references", they are different from regular cell references.

Cell B15 contains a drop-down list with this formula:

=INDIRECT("Table1[First Name]")

How to populate a drop-down list with values from an Excel Table row?

The INDEX function is capable of extracting values in a single row from a cell range, combine it with the INDIRECT function and a reference to the Excel Table, please see formula below.

Reference a table row in a drop down list, cell B15:

=INDEX(INDIRECT("Table1"),6,0)

The animated image above demonstrates the drop-down lists, how they work and what they link to.

How to reference an Excel Table in a Conditional Formatting formula?

Simply add the INDIRECT function to each Excel Table reference in your formula, please see formula below.

Conditional formatting formula, cell range A13:C22:

=COUNTIFS(INDIRECT("Table2[First Name]"),$A13,INDIRECT("Table2[Last Name]"),$B13,INDIRECT("Table2[Company Name]"),$C13)

The Conditional Formatting formula highlights a row if a record in cell range A13:C22 is equal to at least one record in the Excel defined Table (A1:C11)

See also: Data validation overview | Data validation formulas

Dropdown lists allow users select a value from a predefined list. This makes it easy for users to enter only data that meets requirements. Dropdown lists are implemented as special kind of data validation. The screen below shows a simple example. In column E, the choices are Complete, Pending, or Cancelled, and these values are pulled automatically from the range G5:G7:

Dropdown lists are easy to create and use. But once you start to use dropdown menus to your spreadsheets, you'll inevitably run into a challenge: how can you make the values in one dropdown list depend on the values in another? In other words, how can you make a dropdown list dynamic?

Here are some examples:

• a list of cities that depends on the selected country
• a list of flavors that depends on type of ice cream
• a list of models that depends on manufacturer
• a list of foods that depends on category

These kind of lists are called dependent dropdowns, since the list depends on another value. They are created with data validation, using a custom formula based on the INDIRECT function and named ranges. This may sound complicated, but it is actually very simple, and a great example of how INDIRECT can be used.

Read on to see how to create dependent dropdown lists in Excel.

Dependent dropdown example

In the example shown below, column B provides a dropdown menu for food Category, and column C provides options in the chosen category. If the user selects "Fruit", they see a list of fruits, if they select "Nut", they see a list of nuts, and if they select "Vegetable", they see a list of vegetables.

The data validation in column B uses this custom formula:

And the data validation in column C uses this custom formula:

Where the worksheet contains the following named ranges:

category = E4:G4 vegetable = F5:F10 nut = G5:G9

fruit = E5:E11

How this works

The key to this technique is named ranges + the INDIRECT function. INDIRECT accepts text values and tries to evaluate them as cell references. For example, INDIRECT will take the text "A1" and turn it into an actual reference:

Similarly, INDIRECT will convert the text "A1:A10" into the range A1:A10 inside another function: 

=SUM(INDIRECT("A1:A10") =SUM(A1:A10)

At first glance, you might find this construction annoying, or even pointless. Why complicate a nice simple formula with INDIRECT?

Rest assured, there is method to the madness :)

The beauty of INDIRECT is that it lets you use text exactly like a cell reference. This provides two key benefits:

1. You can assemble a text reference inside a formula, which is handy for certain kinds of dynamic references.
2. You can pick up text values on a worksheet, and use them like a cell reference in a formula.

In the example on this page, we're combining the latter idea with named ranges to build dependent dropdown lists. INDIRECT maps text to a named range, which is then resolved to a valid reference. So, in this example, we're picking up the text values in column B, and using INDIRECT to convert them to cell references by matching existing named ranges, like this:

=INDIRECT(B6) =INDIRECT("nut") =G5:G9

B6 resolves to the text "nut" which resolves to the range G5:G9.

How to set up dependent dropdown lists

This section describes how to set up the dependent dropdown lists shown in the example.

1. Create the lists you need. In the example, create a list of fruits, nuts, and vegetables in a worksheet.

2. Create named ranges for each list: category = E4:G4, vegetable = F5:F10, nut = G5:G9, and fruit = E5:E11.

Important: the column headings in E4, F4, and G4 must match the last three named ranges above ("vegetable", "nut", and "fruit"). In other words, you must make sure that the named ranges you created match the values in the Category dropdown list.

3. Create and test a data validation rule to provide a dropdown list for Category using the following custom formula:

Note: just to be clear, the named range "category" is used for readability and convenience only – using a named range here is not required. Also note data validation with a list works fine with both horizontal and vertical ranges – both will be presented as a vertical dropdown menu.

4. Create a data validation rule for the dependent dropdown list with a custom formula based on the INDIRECT function:

In this formula, INDIRECT simply evaluates values in column B as references, which links them to the named ranges previously defined.

5. Test the dropdown lists to make sure they dynamically respond to values in column B.

Note: the approach we are taking here is not case-sensitive. The named range is called "nut" and the value in B6 is "Nut" but the INDIRECT function correctly resolves to the named range, even though case differs.

Dealing with spaces

Named ranges don't allow spaces, so the usual convention is to use underscore characters instead. So, for example, if you want to create a named range for ice cream, you would use ice_cream. This works fine, but dependent dropdown lists will break if they try to map "ice cream" to "ice_cream". To fix this problem you can use a more robust custom formula for data validation:

=INDIRECT(SUBSTITUTE(A1," ","_"))

This formula still uses INDIRECT to link the text value in A1 to a named range, but before INDIRECT runs, the SUBSTITUTE function replaces all spaces with underscores. If the text contains no spaces, SUBSTITUTE has no effect.

Practice file

The example file is attached below – check it out to see how it works. If you want to learn the technique yourself, I'd recommend you build a file of your own. The best way to learn is by doing.