Forgive me for this long winded post, but I can't explain in any other way as this is a complex scenario

We have an MC Access DB that holds the values for our data validations and we read these values into our excel at the time a sheet is opened with a macro.

Basically, we have 3 columns that depend on the previous for the values that are selectable.

Application
Area (depends on what Application is selected)
Function Name (depends on what Area is selected)

The validation rules are set in Data Validation Source at creation time based on the values in the Access DB. Basically the data validation source value for Application is determined when user selects "Insert Test Step" button. It then Goes to the Access DB, gets all Applications, and populates the Source value for Data Validation by building a comma separated list from the DB values.

When an Application is selected, Area (Column B) does the same, it goes to Access DB and gets all Areas associated to the Application selected in column A and builds the validation lists from the returned values, same for Function Name, only it is based on the value in Area (column B)

This is all working fine, but when we add to the Access DB, say a new Area is defined for an Application, the sheets don't reflect that new value as the validations are not updated. We have a macro to reload the values, but would need to go cell by cell to update all the validations based on the new values.

Anyone know of a way this can be done quickly/easily without having to iterate through each cell, save it's current value, update the validation source list, and then reselect the saved value?

We have hundreds of saved sheets with the current test values saved, and when we add new DB entries (due to new functionality), updating the current sheets is very time consuming.

I hope I described it well enough that it is clear what we need to do.

Thanks in advance for any assistance.