Is it true that in Excel 2007 you can't do list validation to a table on a different worksheet, only a named range that is not a table?

I enter a Source of =Responses and it keeps complaining "The formula you typed contains an error."

On the worksheet where the table is, I click into the table, go to the Design ribbon, and in Table Name it says Responses. If I open the Name Manager it says there is a named range called Responses that refers to the proper range. What gives?


Um... Yes and no.

It's true that you can't create a list-based data validation field based on a column from a table.


You can use Name Manager to define a named range, and use it to point to a column in a table.


I have a workbook which tracks my fishing history. In this workbook, I have a table called "Fish", with a column called "Type".

In Name Manager, I can create a new named range called "FishList", and make it refer to "Fish[Type]".

I can then use my data validation list to point to "FishList"... and the Name Manager will redirect it to the appropriate column for my table.

  • That was perfect. You're absolutely right, you can refer to a column of a table using the Table[Column] syntax, which can then have its own named range created for it. Thank you. Now that I think about the fact that tables are multi-column entities, of course it doesn't make sense to do list validation directly to a table, even if the table only has one column. But the means to get it working was just not showing up for me. – ErikE Mar 31 '11 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.