Auto update cell value when data validation is updated

[SamPer]

Hello,

I have a various cells that uses a drop down list via data validation from a separate set of cells 'List'.

When I I update any of the cells in my 'List', for example "Apple" changed to "Apples", the cell(s) referencing 'List' does not automatically update.

Is there a way for excel to make these changes to all cells that use the drop down 'List' instead of manually going through the drop down? In my case, this would means 100s of additional 'clicks' to the spreadsheet.

I need to protect my sheet so, find and replace won't work.

Thanks in advance!

 

[rlv01]

I need to protect my sheet so, find and replace won't work.

If you change your list value from "apple" to "apples" then the next time you access the dropdown menu you should see "apples". But if you are asking whether or not it will automatically update all the cells you had previously chosen "apple" for before you changed the list cell to "apples", then no, it will not do that. That's because the cell value does not 'reference' the validation list. The dropdown list is displayed to allow you to make your choice, but then once the value in the cell is set. That's that.

Why not unprotect your sheet, use find/replace and then re-protect it when done. I can't imagine it is something you need to do often. Another way besides find/replace: assuming all the cells are in one column, then a quick way to change them is to enable autofilter on that column, then filter on "apple" and use copy paste to replace those cells with "apples".

If you are familiar with VBA, that's another way to approach it, and the one which holds out the possibility of an automatic update if you change the list value.

 

[jmus]

Hello, I have the same problem, please can you continue with a VBA solution to this?

 

[rlv01]

That was almost 1.5 years ago. My interest in that problem has waned.