Update values when linked data validation list changes

Question

I have a named table on a sheet that I am using to restrict values in another table.  As long as I only change things in the linked table, it all works as I expect.  When I update the table that has the list of values, there is not indication that anything is wrong in the linking table.

Is there a way to make it so that updates to the list are immediately reflected in the linking table, or at least so that there is some way to indicate an invalid value?

Answer 1

Hi,

You may refer to my solution at the following link - http://www.ashishmathur.com/automatically-change-validated-entries-when-source-of-validation-list-changes/

Hope this helps.

Answer 2

Hi Ashish

This is a great solution. Is it possible to reverse it?

What I whant is to be able to copy the validation worksheetand rename, but then I need the Macro to be placed on the validation worksheet. This should then copy the Macro to work with the same Source at all times. I cant figure out how to turn the references around.

Regards

Peter Tranberg

Answer 3

Excel does not have that functionality out of the box. Again, data validation is for ensuring valid data input, not creating a relational database link.

If you need that kind of functionality, you may be better suited with Access instead of Excel.

cheers, teylyn

Answer 4

I guess then, a variation that would work, if Excel supported it, would be to have the dropdown selection create a link to the cell in the table.  Then when the table updates, the linked cells will be updated, too.

Would this require scripting, or is there some way to do that with data validation lists?

Answer 5

Hello,

Data validation is not a relational database. It's just a means of selecting from a few text values to ensure valid input.

When the list source changes, you will need to test existing data to see if it still fits. This can probably be done with a macro. The difficulty will be to put into processing logic which item will replace which. So, If my list initally is

a

b

c

d

... and then I change it to ...

e

f

g

h

... what determines which of the existing values need adjusting?

cheers, teylyn