Forum Discussion
Using drop-down list: retrospectively change responses if entry in data validation source is changed
Hello I have created a drop-down list in excel for all cells in a column, where the entries in the drop-down list are informed by a source table. I can happily change/add entries to the table...
Hi bryony
no, you can't have the selected values changed automatically, when the source list has changed (because the values are static and not referencing a cell).
What I usually recommend for these cases is to create a conditional formatting rule that highlights such entries, where the source has changed.
You can do this easily with a VLOOKUP.
Example:
In the source list in column K, I have changed the third value after they have been used in colum A
=ISERROR(VLOOKUP(A3;$K$3:$K$5;1;FALSE))
Of course, you would need to change the values manually in column A, but at least it's obvious which one are invalid.