Using an Excel Table within a 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.

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]

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.

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

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

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

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:

7 thoughts on “Using an Excel Table within a Data Validation list

  1. David N says:

    Another reason to prefer the Named Range approach is that the trick with normal cell references only works when the data validation cell is on the same worksheet as the Table.

  2. Mike Igglesden says:

    This worked with the named range, but changing the information in the table does not auto update within the chosen cells. For example in my table, one of the cells is M-12, and i want to change that to M12, i was expecting to do this in the table and automatically update in the data validation cell. It is still asking me to re-select the updated M12 within the drop down to make this change.

    • Excel Off The Grid says:

      Hi Mike – Yes, that is what is meant to happen. The cell does not automatically update.

      You can do a retrospective check by clicking Data Validation -> Circle Invalid Data. To remove the highlights again click Data Validation -> Clear Validation Circles.

Leave a Reply

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