Validation popup ending with the reference (A3) of the choosen field (not the value)

Hello

My name is Gaston. I'm not new to this forum, but I rarely ask questions.
This time I'm trying to do something that I thought was customary. I want to make a popup to validate the inputs of some fields.

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.

Gaston Huot

Hi Gaston,

Have a look at the attached - it may help you get to where you want to be.

Hope it helps

Hi CheeseSandWich

You should be a genius!
Thank you so much.

If I may, I have 3 questions:

1- Sometimes, if I think that I have a good way of doing something, and, if nobody seems to do it that way, I ask to myself :"Is it really a good idea of doing it that way ?" Is it ?

2- If I the name of a table (instead of a range), will it work ?

3- Is there a way to do it in a way that would work with Excel in the cloud (I just saw that VBA code is not supported by Excel on the Web)?

Again thank you so much,

Gaston

1, In my opinion it is a way of getting the job done. It is what it is.
2, It may need amending for the table, the only way to know will be to give it a try.
3, No - VBA is not supported in Excel online.

Hi Gaston,

It should not change anything as the code is pulling the Validation.Formula1 for the cell, this means that wherever the validation is being pointed at is the range used. It might be worth looking if your cell validation range is pointed at your new table data. Have you converted it to a table because you are going to add items to the validation list regularly?

Attached is it working with a table.