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-09-19T14:13:36+00:00

    This solved it for me.  I have a zillion drop-downs on my spreadsheet and all of a sudden Excel didn't feel like using them in certain places.  It was almost like it "ran out" of some magical maximum of data validation cells.

    Thanks Fabi_Prado!  Save as Macro-Enabled worked for me!

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

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2014-11-14T19:04:14+00:00

    OH MY GOODNESS, you have saved my DAYYYYYY!!!!

     

    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?

    0 comments No comments
  3. 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 No comments