BiTurbo228
New Member
- Joined
- Sep 10, 2018
- Messages
- 3
I'm having a rather perplexing issue with data validation lists on excel 2010 intermittently disappearing/breaking after a file is saved and reloaded.
Every now and again after saving and closing my workbook, one or more variants of my drop-down lists will break. The arrow to select the list is still there, but no list appears when it is clicked (or an entirely blank list appears).
Initially, I had a backsheet with lists that the data validations on each tab referenced. After saving and closing, sometimes a validation list would lose it's 'Backsheet!' reference and keep the cell references, just on the same worksheet resulting in a blank list.
I next changed all my validation lists to be on the same workbook as the list itself in hidden columns. Unfortunately, every now and again a list would break again. It would seem to either lose half of the cell references resulting in an abridge list, or lose the references entirely and just having '=REF!' in the validations box.
Next, I tried to do the most basic validation list possible and manually enter the acceptable inputs into the data validations box like so: 'Please select,1,2,3,4,5' etc. That seemed to work for a while, but I've just opened it again and the same issue as above has just happened.
I'm using Excel 2010 and re-opening on 2010. The files are saved and loaded from a shared work drive. I'm saving as a .xlsx file, and I've tried saving as a .xlsm file to no effect. Display objects is ticked to appear. No macros on the spreadsheet. I've tried opening and repairing which doesn't restore the missing drop-downs.
Only thing I haven't yet tried is copying everything over to a brand new workbook which I'd like to try and avoid as it's a massive thing!
Help!
Every now and again after saving and closing my workbook, one or more variants of my drop-down lists will break. The arrow to select the list is still there, but no list appears when it is clicked (or an entirely blank list appears).
Initially, I had a backsheet with lists that the data validations on each tab referenced. After saving and closing, sometimes a validation list would lose it's 'Backsheet!' reference and keep the cell references, just on the same worksheet resulting in a blank list.
I next changed all my validation lists to be on the same workbook as the list itself in hidden columns. Unfortunately, every now and again a list would break again. It would seem to either lose half of the cell references resulting in an abridge list, or lose the references entirely and just having '=REF!' in the validations box.
Next, I tried to do the most basic validation list possible and manually enter the acceptable inputs into the data validations box like so: 'Please select,1,2,3,4,5' etc. That seemed to work for a while, but I've just opened it again and the same issue as above has just happened.
I'm using Excel 2010 and re-opening on 2010. The files are saved and loaded from a shared work drive. I'm saving as a .xlsx file, and I've tried saving as a .xlsm file to no effect. Display objects is ticked to appear. No macros on the spreadsheet. I've tried opening and repairing which doesn't restore the missing drop-downs.
Only thing I haven't yet tried is copying everything over to a brand new workbook which I'd like to try and avoid as it's a massive thing!
Help!
