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!
As Wyn Hopkins has pointed out, DV now at least recognises that table columns expand, so an absolute reference to a range that comprises an entire table column will alter as the table resizes - but why not take it a step further and actually use the structured table reference - it would be less ambiguous.
Craig Hatmaker commented
Structured references make formulas self documenting. Please extend the self documenting concept to data validation list sources by accepting structured references there as well.
Wyn Hopkins commented
The Current Channel version now allows referencing to the cells in a Table column ( and the validation picks up the new items ) https://youtu.be/MD_iLh-UEmQ
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