I have a column that uses values from predefined set (a drop-down list), implemented using Data Validations. Now if I change some value in the list, the drop down (when open) will immediately offer this new value. However, the table will not auto-update itself, meaning that some of the values in the column will be invalid until I fix them manually.
I have seen solutions based on macros / VBA but isn't there some clever way directly in the Excel UI?
Best Answer
As i mentioned in the comments, the only way to do this would be with VBA.
Here is one option. I've added comments throughout the code. This assumes that you are using a named range for the validation list named "List" and that it is on the same sheet as the cells being validated.
You can also download the example spreadsheet I put together to test this out. (Contains macros!)