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!
Somehow the data validation does not know structured references when used as a list source.
My current solution is to give the part of the table a name to a structured reference!!!
The functionality of the data validation window has not changed for decades!
I would add my support to this. It is such a needed improvement. Data validation lists and table are super powerful ways of controlling and managing data sources and work together to control data input and output - except they currently don't work together! Why not Excel Team? Please, please add the ability to use table referencing in all Excel features, but especially data validation formulas.
The current best workaround is the INDIRECT() function, but that adds a lot of bulk to the validation list formulas and provides another place for breakage if the reference table or columns ever get renamed.
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