Can Data Validation create a reference instead of copying the cell value?

Question

My Goal is to create a range of cells (let's say A1:A10) where you can select the value from a drop-down list (this uses data validation), but if I change the value in the source list (let's say S1:S3), then the cell values which are already selected in the "A" column should adjust to the new values as well.

As far as I can tell the Data Validation function just copies the current value of the selected source cell to the target cell. But this doesn't update when the source changes, as it is only a value.

I want to create a pointer/reference to the selected source cell instead, which would update with the source.

Excel can make references with the INDIRECT function. If the source cell values are given as an INDIRECT of some other data, the data validation still only copies the current value.

If in the data validation setup window I define the Source as an INDIRECT value, it still only copies the current value.

(To clarify: the options in the drop-down menu update correctly. I want the already filled in cells to change their value in the "A" column.)

Is there a way to make the data validation create a reference to the source instead of copying the current value?

Answer 1

Hi Dagnaren

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself no one here is a Microsoft employee.

There is no direct way to make the data validation create a reference to the source instead of copying the current value. The data validation function is designed to copy the value from the source list to the target cell, not to create a link or a formula. Therefore, even if you use the INDIRECT function or any other function in the source or the target, the data validation will still copy the value, not the function.

You can use a combination of the INDEX and MATCH functions in the target cells instead of the data validation function. For example, if you have the source list in S1:S3, and the drop-down list in A1, you can use the following formula in B1:

=INDEX(S1:S3,MATCH(A1,S1:S3,0))

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi AnnaThomas,

Thank you for the reply!

It's a shame that the data validation can't be altered. I hoped there would be some setting option for it somewhere. Technically it should be possible.

I have tried your INDEX&MATCH idea, and although it could replace the data validation itself, I couldn't make it work for my purposes.
Also the drop-down list of data validation is a high priority requirement from my user.

Thanks for the idea regardless! :-)

Best Regards,
Dagnaren