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:
Posts related to data validation and structured references:
In December 2020, we posted that this issue was fixed, but a few months later we realized that the change caused an unwanted side-effect, so we had to undo the fix.
We’ll keep monitoring votes on this. Sorry for the inconvenience caused by fixing it and then taking away the fix, but the side-effect of the fix was worse.
The side-effect was that in some cases, adding a row to a table on one sheet could cause a data validation rule to be applied to cells on another sheet where it should not apply.
Steve [Microsoft Excel]
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.