Using drop-down list: retrospectively change responses if entry in data validation source is changed

[bryony]

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 & the drop-down list will change for future selections.

If I change the way something is typed in the table, is there any way to get it to retrospectively change the cells in which I have previously selected that option?

For example, an entry in the table was originally "Prevalence" & I have now updated it to "Prevalence surveys". Where I have previous selected "Prevalence", could I get these to automatically change to "Prevalence surveys"?

Thank you for any help!

[#2]

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.