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

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 2

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