This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.
Hi, I have been having the same problem. I have just saved the Excel file as a Macro Enabled workbook and problem has resolved.
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.
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
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
I'm more than 6 years late to the game, but in order to do this (I had the same problem for one column but not another on the same worksheet, which completely played with my mind), you'll have to select the range of cells and name them, which can be done by locating the name box on the far left of the formula bar, and entering a name for the cells you selected (example "Status"). Then redo all the steps regarding data validation as you had done, but this time, when you highlight the referenced range, the "name" of the range you selected will appear in the Source box, as opposed to a range (example B3:B6). I believe this helps the sheet reference saved cells as opposed to just a range of data, as this has alleviated the issue for myself, as I've saved/closed/reopened and even copied the worksheet to a separate file and it's functional.
Hope this helps.