This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Using an Excel Table within a Data Validation list

Excel Table within Data validation list

Excel Table within Data validation list

Excel Tables expand automatically whenever new data is added to the bottom.  This feature alone makes Tables one of the most powerful tools within the Excel users toolkit.  A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature.  Data Validation lists would also benefit from the auto-expand feature… but it just doesn’t work; an oversight on Microsoft’s part (in my opinion).

The following Data Validation settings look like it should work.  It should use the column called Animals from a Table called myList.

Data Validation Table Ref Error

But Excel will show an error message instead.

Data Validation Table Ref Error Message

What can you use to solve this problem?  I’ve got three solutions for you:

  • Normal cell references over a Table
  • Named Range of the Table column
  • INDIRECT function

Let’s look at each of these and you can decide which is the best option for your situation.

Example Data

The examples in this post use the following data:

Table Source List Data Validation

The Table name is myList (I know, it’s not a great name, but it will work for the examples).

 

Normal cell references over a Table

If we use standard cell references, the Data Validation list will be static.  Each time a new item is added to the list either the Data Validation source will need to be updated, or the new items will need to be added within the existing list range.  However, if we create the Data Validation list using normal cell references over a Table column, it will expand.


Advertisement:

Look at the screenshot below.  The cells $A$2:$A$7 are the same cells as the reference myList[Animals] would be for a Table.

Data Validation Cells over a Table

It looks like it would behave like a standard static list.  But look at the video below… it actually expands!!

Data Validation - Table using normal cell references

This method only works if the cells selected include the entire Table column (i.e., if you reference $A$4:$A$7 it will not expand when a new item is added as it is not the whole Table column).

 

Named Range of the Table column

Named Ranges will accept a Table column as the source, and a Data Validation list will accept a Named Range.  So we can take a two-step approach to achieve the same result.

 

Create the Named Range

To create the Named Range click Formulas -> Name Manager.

Name Manager - New

The Name Manager window will open.  Click on the New… button.

Name Manager - New

The New Name window will open.  Give the Named Range a name (myNamedRange in the example below), and set the Refers to box to the name of the Table and column.

Name Manager - New Name

The formula used in the screenshot above is.

=myList[Animals]

Advertisement:

If the list only has one column, it is possible to refer to just the Table without the column name (just like the screenshot below.

=myList

Finally, Click OK.  The Named Range has now been created.

 

Add the Named Range to the Data Validation list

The screenshot below shows the Named Range added to the Data Validation list box.

Data Validation with Named Range

The Data Validation list will now expand whenever new items are added to the Table.

 

INDIRECT Function

The INDIRECT function will convert text into a Range which Excel will recognize and use in calculations.  For example, if Cell A2 contains the text “A1” you can write =INDIRECT(A2) and Excel will convert that to the cell reference =A1.

We can use the INDIRECT function with the Table and column reference as text and Excel will understand this.

Data Validation Table - INDIRECT

The formula used in the screenshot above is.

=INDIRECT("myList[Animals]")

If the list only has one column, it is possible to refer to just the Table.

=INDIRECT("myList")

The Data Validation list will now expand whenever new items are added to the Table.

 

Which method to choose?

So with three options to choose from, the question is which is the best.  Personally, I prefer the Named Range option.  I use Named Ranges often, so it fits with my way of working.  However, if I have to create a lot of Named Ranges, I may be lazy and choose the INDIRECT method.  I don’t tend to use the normal cell references method; I just don’t understand how or why it works (it really shouldn’t… but it does).


Advertisement:

I’m not aware of any technique having a significant advantage over another, so pick the one you find the best.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *