Excel 2010 Drop-Down disappears when file is saved/reopened

Anonymous
2010-12-02T17:45:10+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-01-31T09:48:34+00:00

Hi, I have been having the same problem.  I have just saved the Excel file as a Macro Enabled workbook and problem has resolved.

Was this answer helpful?

300+ people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-12-03T14:29:47+00:00

Opened workbook by filename 'TEST.xlsx'.

Entered an sample list of names in Sheet2 A1:A10

Selected cell A1 in sheet1> data tab> datavalidation> list> source =Sheet2!A1:A10 (Check in cell drop down)>ok

Sheet1 A1>Format cells> protection> uncheck loceked> ok.   Now when sheet1 is active> Protect sheet>ok; protect workbook> ok

I saved an reopened it, it worked . It worked like a charm.

Please provide step by step on how you trying to work on excel file. I am sure some where its problem in the course of data validating:(

Keep Eye on your TIME, Not on your WATCH.

Was this answer helpful?

100+ people found this answer helpful.
0 comments

43 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-08-01T11:03:33+00:00

    In case it helps, I had this issue this week with 2010 excel  and just needed to make sure I was saving file with extension (*.xlsx) instead of (*.xls).

    :D

    Any idea how to solve this with files stored in Sharepoint? This works for me with excel files stored in MyPC but when I open the file stored in Sharepoint, it prompts me to recover the file and gets rid of Conditional Formating and Lists....

    Thanks

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-12-04T15:06:14+00:00

    Awesome!!!

    You just Save me 4 days of work!!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-01-15T22:01:56+00:00

    Was it referred to any other workbooks ie, data validation?

    Keep Eye on your TIME, Not on your WATCH.

    Was this answer helpful?

    0 comments