Excel how to propagate changes

I have a list, e.g., siteList (a named range) in one workbook mddata.xlsx file.

Now I am using this siteList in many data validation lists which exist in many other work books.

If I make a change in mddata.xlsx file; how can make changes propagated in all the workbooks referring to siteList. I know when new values are entered; data validation list will show the changes; but the values which were already selected; changes do not show up? Is there a way to automate those changes?

Hi pmehrotr

If I understand correctly, you want to have a validation list (say) dog, cat,mouse. You want to delete cat from the validation list and then show all cats, in various workbooks as being invalid?

As you have probably found, the Data Validation only works on entry, so it will not show changes in the entered cells. You are looking at having to get some VBA, but even then you will have to specify the workbooks and the worksheets involved.

(Unless anyone else knows anything to the contrary?)

Regards
Alastair