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
    2014-03-28T21:59:55+00:00

    In Excel 2010 64 bit:

    Tried all foregoing. Repaired the file.

    Nothing helped.

    This worked:

    Go to file.options.Advanced:Display options for this workbook. Set for objects, show "All".

    Somehow when the file was damaged this option was set to "Nothing". Repair did not correct this.

    That works!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-01-29T20:55:13+00:00

    I am having the same issue- data from drop down menu clears, but have tried saving as Macro enabled and checked that the Display Objects is set to All.

    Anyone have any other bright ideas?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-01-10T21:25:51+00:00

    I was facing the same issue, and just solved after saving as .xlsm

    I'm sorry it did not work for you. But a .xlsm file is the same thing as a excel macro-enabled file (workbook).  http://office.microsoft.com/en-us/help/introduction-to-new-file-name-extensions-HA010006935.aspx

    Either way, I'm glad you got it to work !

    Was this answer helpful?

    0 comments