Data Validation Stops Working in Column A only.

Question

I have created an automated workbook for daily reports in Excel 365 that relies on validation boxes linked to dynamic arrays. The problem is that after some use, and under no specific set of conditions, the validation boxes in column A all stop working. No other validation boxes on the sheet are affected.

Please note that:

1. Validation boxes are not handled by VBA.
2. They are in merged cells (as are a lot of other validation boxes throughout the sheet).
3. Drop-down list contains between 10 and 40 items, depending on conditions.
4. There is nothing unique to those specific validation boxes that could be considered as trigger points for the error.
5. Problem is repeatable and normally occurs when more than one workbook is open and when Excel has not been completely closed for a number of file open/close events (events not related to where the errors occur).
6. Office is updated, repaired and affects any user of the workbook across our organization (not computer specific).
7. Problem does not occur on a Mac (tested with 3 separate computers) and is clearly Windows specific.

All versions are Microsoft 365 Enterprise MSO (Version 2407 Build 16.0.17830.20210) 64-bit

The present solution is to close all workbooks and close Excel. Reopening the workbook and Excel fixes the problem.

Obviously not an intended function of the program...

Any advice to avoid such issues would be appreciated.

Answer 1

Could you share a test file to reproduce your issue?

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.