Data validation list sources referring to tables are only dynamic for tables on the same sheet
Data validation still does not support structured references as list sources. Luckily, the non-structured references to table columns update if the column changes size. However, this only occurs if the data validation cell and the table are on the same sheet. This is further incentive, beyond the usual readability issue, for Excel to support structured references in data validation.
You can download a demo file here:
http://www.mediafire.com/view/hrz90a8u7kk456p/DataValidationTable_Update.xlsb
Posts related to data validation and structured references:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9018427-improve-data-validation
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10561302-data-validation-allow-table-references-as-list-s
Good news. While data validation still does not support structured references, the problem of references not adjusting if the table is on a different sheet has been fixed. With version 16.0.133 or greater, if you refer to a column on a different sheet than the cell with the data validation rule, it will behave the same as if the table is on the same sheet, expanding the reference if you add rows to the table, or reducing the reference if you delete rows from the table.
Here’s a post about it on the Excel Techcommunity site – https://techcommunity.microsoft.com/t5/excel/dropdown-list-improvement-data-validation-lists-are-dynamic-for/m-p/1977541.
We’re marking this as Complete since the title refers to the exact issue that was fixed.
Steve [Microsoft Excel]
2 comments
-
Kenneth Barber commented
In response to Bryon, the workaround is to add a named range that refers to the table column, and then use that named range as the source of the data validation dropdown lists. Then you will have a dynamic dropdown list and the table can be on any sheet.
-
Bryon Smedley
commented
This is quite odd. I prefer to have my validation lists hidden on a separate sheet, but have to use convoluted formula solutions to make them dynamic. I can use a Data Table to make it dynamic, but it can no longer be on the hidden sheet. It would be nice to be able to use structured references for dynamic drop-down lists.