5

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?

Thanks.

5 Answers 5

8

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

1
  • This is the best answer for those using Excel versions 2010 and later. It's the fastest method available and unlike the INDIRECT method it doesn't break if you rename your table or a table column.
    – ChrisB
    Jun 29, 2018 at 19:02
6

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:

=INDIRECT("Table1[MyColumn]")

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

References

  1. https://support.office.com/en-nz/article/INDIRECT-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd?ui=en-US&rs=en-NZ&ad=NZ
  2. http://www.get-digital-help.com/2012/10/15/how-to-use-a-table-name-in-data-validation-lists-and-conditional-formatting-formulas/
3

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.

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

Reference: http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/

1
  • 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. Jun 15, 2014 at 18:19
0

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: http://www.ozgrid.com/Excel/DynamicRanges.htm

You must log in to answer this question.

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