Forum Discussion

bryony's avatar
bryony
Copper Contributor
Jan 28, 2022

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 & 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!

excel

1 Reply

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jan 28, 2022

    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.

     

     

     

Resources