Data Validation Lost on Reopening

Question

I have added some data validation (an in-cell drop-down) to my worksheet, but when I save it, close, and reopen, the validation is gone. I don't even have to shut down Excel - just closing the spreadsheet and reopening it makes the validation disappear.

More info: this is a 600-record spreadsheet with several formulas. It's saved on a network and accessed by multiple people, although only one person can have it open at a time. The drop-down values are saved on a second tab and the validation is set to a list, and a message that pops up when you enter the cell, but no errors (i.e., someone can either select from the list or type their own thing in). It all works perfectly until I open it up again.

There are also a couple of simple macros on the spreadsheet - not sure if this makes a difference. (Just one to remove the filters so it can be done quickly before re-saving a file, and another to jump to the bottom of the spreadsheet to ease data entry.)

I have tried doing an "Open and repair" to no avail.

Suggestions very welcome!

Jen

Answer 1

Just wanted to say that I was able to track down an answer to this problem on another forum. Apparently it has to do with the file being an xls file (I was working in Excel 2010, but the file was created in an earlier version). I updated the file to an xlsm file - since it contains macros - and now it seems to retain the data validation info correctly.

Answer 2

Hi,

Glad to know that you were able to resolve the issue and thank you for sharing the resolution as this would help other users.