Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to validate cell values with the help of the simple list. But it turns out that it's difficult to extend this list without necessity of updating validation source range every time new value is added as shown on the picture.

alt text

My question is: could I put dynamic table reference into the 'source' field? If yes, then how could I do this?


share|improve this question

As of Excel 2010, you can't directly use structured references as the source list for data validation. However, you CAN define a name for the structured reference, and use this new name instead.

For example, if I have a single-column table named Table1 that contains a column named Acceptable Values, I could define a new name ValidValues using the structured reference Table1[Acceptable Values]. In the source field of the data validation list I can enter =ValidValues

share|improve this answer

I can see two ways to do this:

Use a Named Range

Select C1:C6, on the menu bar goto Insert -> Name -> Define, enter a name for the range (eg, "ValidationList"). On the Data Validation window you have above the Source field should then be set to =ValidationList.

Whenever you add items to the list you need to revisit the Insert -> Name -> Define window, select the named item from the list and change the Refers To value as required.

All references to =ValidationList will then automatically point to the newly modified list.

Extend the Center of the List

In this example you are using $C$1:$C$6 as the list area, if you insert new cells in the middle of this list (ie, select C4, right click, insert, shift cells down) then the range should be expanded for you.

share|improve this answer

To answer your question:

could I put dynamic table reference into the 'source' field? If yes, then how could I do this?

Yes! Like this...

Reference the table column directly from the data validation source field, using the INDIRECT function.

Example Data Validation Source:


Just be careful: any changes to the table/column reference names will not be magically updated!


share|improve this answer
  1. Create table that will hold the choices for drop down menu.

  2. Go to Formulas > Name Manager > New Name, and define a name for the table column that you want to use to hold your drop down list choices.
    New Name Dialog

  3. Select the range/cell that you want to turn into a drop down list and add data validation as normal, except that in the source you are going to use the name you defined earlier (ex: =ListChoices).


share|improve this answer
I do not know why this was at zero. This is the correct answer. I just tested it and it does exactly what OP wants: A dynamic validation list linked to a table column. – Martín Valdés de León Jun 15 '14 at 18:19

I want to extend DMA57361's answer. Use a named range but make that range dynamic so that when the list grows or shrinks the dynamic range expands and contracts as you want it to be.

If you have a list starting at C1 and ending at C6 the following code will allow it to expand and contract. Once a cell is left blank, the list will stop.

=OFFSET($C$1, 0, 0, COUNTA($C:$C),1)

Place this formula into a named range and call it ValidationList. In the data validation dialog select list and then enter =ValidationList as the source.

A really good resource to create dynamic named ranges:

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .