I have a workbook created in Excel 2010. I used Data Validation to create a drop-down list in a cell that uses a different column of data for the list of values. I selected "List" in data validation and made sure that "in cell drop-down" is selected. the Drop Down list works fine while I have the spreadsheet open. For business purposes, I need to protect both the worksheet and workbook structure but the drop-down cells are unlocked and not hidden. The source data is both locked and hidden. Everything works fine until I save and close the workbook and then reopen it. The drop-down arrow still appears but the list does not pop up when the cell/arrow is selected. When I select "Data Validation" again, it says it allows "Any Value". That is, the validation is gone.
I know in excel 2007 there was an issue with frozen panes using drop downs. i have no frozen panes. the cell DOES, however, have a name applied to it so it can be referenced by name in other places in the workbook. But other drop-downs without names also have the same problem.
Please help. I am really under the gun to get this working and will be completely stuck without these data validation fields.