Popup validation process ending with reference instead of the value.

New Contributor


My name is Gaston

I'm trying to do something that I thought was customary, but is not. I want to make a popup to validate the inputs of some fields, and keep the reference from the list instead of the value.


I know the validation function and I use it often. In this case, I wanted to keep the address of the chosen value, and not the chosen value. In the end, it's harder than I thought...


Basically, I want to build a validation lists/process that will be used in various places in my spreadsheet. The difference from the usual way is that I want to keep the address of the value and not the value. So if a correction is made to the value in the validation list, it will end up in all cells that use that choice, and I'm unable to figure out how to do that.


Let's suppose the validation list, in the table "Fruits":
A Fruits
B Apple
C Orange
D Banana
E Grape


Using the standard popup validation process, I will store, as an example, the value "Apple" in the resulting field. If I change "Apple" to "Apples" in the validation list, it will stay "Apple" for all existing fields. I want these fields to change for "Apples" because this is the new reference. I don't want to store "Apple", but "=B1" (?) in the resulting fields.


How can I do that ?


Thanks for your help.


0 Replies