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 No 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 No 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 No comments

43 additional answers

Sort by: Oldest
  1. Anonymous
    2014-01-10T19:16:59+00:00

    It did not work for me.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2014-01-10T19:24:06+00:00

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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No 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 No comments