Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Using an Excel Table within a data validation list

Data Validation with Excel Tables

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 with Structured Reference

But Excel will show an error message instead.

Data Validation - 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.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0076 Data Validation with Tables.xlsx

Watch the video


Watch the video on YouTube.

Example Data

The examples in this post use the following data:

Source Data for Data Validation List

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.

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.

Standard Cell references for Data Validation

It looks like it would behave like a standard static list.  But somehow Excel knows, and the data validation list expands when values are added to the Table. Try it for yourself.

Problems with this approach

There are some big problems with this approach:

  1. If the data validation list is on a separate sheet to the Table, it will not expand.
  2. This method only works if the cells selected include the entire Table column (i.e., in our example, 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).

Due to the issues noted above, it is probably best to avoid this method.

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.  Therefore, we can take a two-step approach to achieve the same result.

Create the named range

To create the named range, click Formulas -> Define Name

The New Name window will open.  Give the named range a name (myDVList in the example below) and set the Refers to box to the name of the Table and column.

Finally, Click OK.  The named range has now been created.

Create Named Range for Data Validation With Structured Reference

The formula used in the screenshot above is.

=myList[Animals]

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

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.

Key points about this method

In relation to this method, there are a few things to be aware of:

  • This is a two-stage process, which takes a little longer to set-up.
  • If the Table or Column names change the named range automatically updates to reflect the changes.

While it takes longer to set-up, this method is the one that is least likely to break.



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.

INDIRECT method for creating a Data Validation list from a Table

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.

Warning about this method

There is one key thing to be aware of; the Table and column names are now hardcoded into the INDIRECT formula. If either of these change the data validation list will cease to work as the formula cannot find the source data.

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 advise using the standard cell references over a table as it won’t work across worksheets.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

One thought on “Using an Excel Table within a data validation list

Leave a Reply

Your email address will not be published.