Data validation - allow Table references as List Source.
If I'm using Data Validation in List mode, and I use a Table reference (e.g. "=Table1[Column1]") as the source, for some reason it doesn't work!
The workaround is to create a separate named range for that Table reference (e.g. make a named range called "Table1Column1" that Refers to "=Table1[Column1]", but it's a real pain to have to do this every time, and it is something that I use a lot!
Andrey Minakov commented
And using a name of a table (with one column without a header row) is also impossible!
the indirect version will not work if you get the table name via a lookup, it only works via normal reference
Work around is using the formula =INDIRECT("Table1[Column1]") as the list source.
That saves you from creating the named range.
Alex Wilding commented
I wholeheartedly agree with your suggestion Graham. Having recently discovered table references and how elegant they are, it's a shame they can't be used for data validation lists. Yes, there are workarounds, but they're not as good.
you must used a maned range of the table data
LG DES commented
I agree, and the workaround generate another problem: When duplicate a sheet, all workbook names (that references a Table) will be duplicates in a worksheet.
Link provided is not the same question, its about Autocomplete, we talk about DataValidation can't use a Table Reference.
Kenneth Barber commented