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

%3CLINGO-SUB%20id%3D%22lingo-sub-3079436%22%20slang%3D%22en-US%22%3EUsing%20drop-down%20list%3A%20retrospectively%20change%20responses%20if%20entry%20in%20data%20validation%20source%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3079436%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20drop-down%20list%20in%20excel%20for%20all%20cells%20in%20a%20column%2C%20where%20the%20entries%20in%20the%20drop-down%20list%20are%20informed%20by%20a%20source%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20happily%20change%2Fadd%20entries%20to%20the%20table%20%26amp%3B%20the%20drop-down%20list%20will%20change%20for%20future%20selections.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20change%20the%20way%20something%20is%20typed%20in%20the%20table%2C%20is%20there%20any%20way%20to%20get%20it%20to%20retrospectively%20change%20the%20cells%20in%20which%20I%20have%20previously%20selected%20that%20option%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20an%20entry%20in%20the%20table%20was%20originally%20%22Prevalence%22%20%26amp%3B%20I%20have%20now%20updated%20it%20to%20%22Prevalence%20surveys%22.%20Where%20I%20have%20previous%20selected%20%22Prevalence%22%2C%20could%20I%20get%20these%20to%20automatically%20change%20to%20%22Prevalence%20surveys%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3079436%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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!

1 Reply

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

DTE_0-1643381384220.png

=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.