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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-01-28T09:37:39+00:00

    Hi,

    I'm facing exactly the same problem. Has someone find out a solution yet? I'm using Data Validation to create a drop-down list in a cell. Whenever I close the workbook, the Data Validation disappears.

    Hope someone can help.

    BR,

    Gabriel

    Was this answer helpful?

    30+ people found this answer helpful.
    0 comments
  2. Anonymous
    2011-01-31T09:49:25+00:00

    I have had the same issue and i have just saved the file as a macro enabled workbook and this has solved the problem.

    Was this answer helpful?

    30+ people found this answer helpful.
    0 comments
  3. Anonymous
    2012-03-13T21:29:59+00:00

    The problem occurs when working in Excel 2007 compatibility mode, and it occurs on the third drop down list created. Suggest you try your test again...

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments