Putting table name as a reference for the purpose of cell values validation

Question

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.

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

Thanks.

Answer 1

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

Answer 2

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/

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

Answer 4

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.
   

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/

Answer 5

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