Jan 28 2022 04:13 AM
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!
Jan 28 2022 06:51 AM
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.