Thread: Data Validation List from Table Name Error

Hello,
I have had no trouble creating drop-down tables in the past, but I am having difficulty when the list data is coming from elements of a table that has been 'Formatted as Table'.

A simple example of my problem is this:

1. Create a vertical list of 2 elements with some heading, e.g. 'MyTable' \ A \ B.
2. Format as table with any of the styles. Select 'my table has headers'. The A and B cells together have been automatically named 'Table1'.
3. Select any other cell, and apply data validation, allowing for list with source =Table1
4. I get the 'The formula you have typed contains an error' dialogue and can't continue.

If I add another name to cells A and B, such as Table2, it will let me validate using that name. However, the two are fundamentally different (I can't delete the Table1, and it is called a 'Table Name' rather than a 'defined name')

Is there some reason I can't seem to use table names to identify source data for validation? I could of course name them twice, but this would be a pain and clutter the spreadsheet.

Any ideas?

PS Hi!

Hello Rosilisk,

If you have more than 1 column in Table1, can't use list source as Table1. Define a name range of 1 heading & use it in the list source Or use INDIRECT in list source.

Say, A & B are the 2 headings in Table1, so use in list source,

=INDIRECT("Table1[A]")