Data Validation List Updates

You are correct. Once you have selected a value in a Data Validation dropdown list, that value will not automatically change or be flagged as invalid if you change the source for the list.

To do something like that requires macros. If you would like help with doing this with macros then attach your file and describe what you are doing.

Using a list in one workbook to populate data validation in another adds a whole new dimension to this problem. Plus you have a lot of these lists. I'm not saying it can't be done but I am not able to make the time to do this in a free forum.

The strategy would be if you make a change in the Programming Worksheets file that affects a list used by the other file, then the code would have to open the Space Data file and check for all the places that list is used, and check to see whether the change affected any of them, then prompt the user to enter a replacement.

If you change one entry in a list, and want any selected items with that entry to be changed to match, that could be done too, but you would have to be specific about how to tell the difference between that and deleting entries. For example, if your list is

Auditorium #G101
Auditorium #G113
Band #G122
Changing

Suppose we rename Band #G122 to Band Room #G122. Anywhere the user has selected Band #G122, you would want it to be replaced by Band Room #G122.

Now let's say we want to remove Band #G122. Now Changing will be the third item. You have to have a way to distinguish between removing Band #G122 and requiring the user to enter a new space, vs. changing all user selections of Band #G122 to Changing.