Closed Thread
Results 1 to 14 of 14

Data Validation Drop Down list disappeared

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172

    Data Validation Drop Down list disappeared

    I have a problem, I have a massive excel sheet with Drop down lists in many places, but when I opened it today the cells with the data validation drop down lists are not working. Meaning when you click on them (the cell) the drop down doesn't show up. When you go to Data / Validation on the cell you see that it is referencing the list, but the drop down still doesn't work. I have clicked the little box next to In-cell dropdown and still nothing. I can't even add a new drop down list to this worksheet.

    When I add a new one, it's the same problem. The list is there in the Data Validation area, but no drop-down coming up in the actual cell on the spreadsheet.

    Anyone know why the drop down would disappear and how do you bring it back. Thanks.

  2. #2
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172
    okay, I have figured this out and will post my respond since the next guy with a smiliar problem will have an answer.

    Basically I had to copy and paste this sheet in a new spreadsheet to make it have the drop down lists again. In the new sheet the drop downs were working and referencing their list in the data validation table perfectly. So if this happens to you that would be your best bet, copy and paste the sheet into a blank sheet.

  3. #3
    Forum Contributor
    Join Date
    03-14-2005
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    172
    no, I mean the drop down menu is just not there.

    I did use list from another sheet with named ranges and all, but the when you click the cell (with the data validation lists) there was no drop down coming up with all the choices you had (the named range in the other sheet). Meaning the dropdown disappeared, like I said above.

    So to make the drop down boxes reappear, I had to copy the corupt sheet (the one without the drop down menu) to a new sheet, and viola! the drop down menu was back for the cells that had data validation lists associated with them.

  4. #4
    Forum Contributor
    Join Date
    08-29-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: Data Validation Drop Down list disappeared

    Sorry to resurrect an old post, but this information is pertinent and still necessary. I just had this happen to me. Was editing a sheet that for whatever reasons works fine on my wifes computer, but on mine lags to hell and back, almost unusable with no apparent reason. I made some small edits on the area where the data validation was and the arrows disappeared completely in the cells with data validation even though the other sheets were working fine (this is a calender / budget sheet), so I did what you said and just copied each sheet into a new workbook and the problem fixed itself and the sheet is no longer slow as hell on my computer. No idea what could have been causing this.

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    Hey everyone... this has happened to me a few times now and I almost forgot how to fix it so I am posting this now so it will be immortilized and no one will have this issue again!

    For those who say you have to create a new sheet - nahhhh you don't. This is how you fix it:

    Under File - go to Options, and then go click on Advanced. A big list of things you can check or uncheck will show up.

    Go down to "display options for this workbook" and check "All" under "for objects, show:"

  6. #6
    Registered User
    Join Date
    04-05-2016
    Location
    ahmedabad
    MS-Off Ver
    2010
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    it was a hell restoring back the drop down list before reading your above lines.

    life now easy.

    thanks.

  7. #7
    Registered User
    Join Date
    06-07-2018
    Location
    London
    MS-Off Ver
    MS Office Professional Plus 2010
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    Quote Originally Posted by nwindsor View Post
    Hey everyone... this has happened to me a few times now and I almost forgot how to fix it so I am posting this now so it will be immortilized and no one will have this issue again!

    For those who say you have to create a new sheet - nahhhh you don't. This is how you fix it:

    Under File - go to Options, and then go click on Advanced. A big list of things you can check or uncheck will show up.

    Go down to "display options for this workbook" and check "All" under "for objects, show:"
    It worked for me with my MS Office 2010 as well Thanks dude!

  8. #8
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Data Validation Drop Down list disappeared

    nwindsor still getting points for the win! This just helped me with the same issue. Now the question is, why did it happen to begin with?

  9. #9
    Registered User
    Join Date
    03-23-2011
    Location
    Liverpool UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Data Validation Drop Down list disappeared

    Same problem in 2016.

    The objects restore solution worked fine.

    As many have said why did it turn off in the first place?

    It was immediately after entering a VLOOKUP formulae ??

    Thanks for the fix!

  10. #10
    Registered User
    Join Date
    11-10-2020
    Location
    Austin, Texas
    MS-Off Ver
    Microsoft 365
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    The solution posted by nwindsor worked for me too. The other solution of copying everything to a new workbook works by inadvertence in that a new workbook has “For objects, show:” set to “All” by default, but this is a painful solution. As for why the dropdowns disappeared in the first place, I think you probably accidentally hit Ctrl+6 as this is the shortcut for hide/show objects. I was experimenting with shortcuts, like Ctrl+1 is Format Cells, Ctrl+2 is Bold, then Italics, Underline, Strikethrough. But Ctrl+6 hid objects and I didn’t realize it until later. In general, it’s not a great idea to find keyboard shortcuts by randomly trying them to see what happens. Better to find a list online. So if your data validation dropdowns have disappeared, try Ctrl+6 to show them.

  11. #11
    Registered User
    Join Date
    01-16-2021
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    I had a similar problem, only my cells had lost altogether the data validation (and the "For objects, show" option was set to "All"). I created the file and used it for months (maybe 2-3 times a month) on a certain PC, then copied it & used it on another PC for about 10 days more intensely, then back to original PC and the data validation was instantly gone... Interestingly, this was the case for all recent back-up files I had created in the new PC, when I tried to open them again in the old PC.

    I couldn't solve it following the above advice, so I actually manually copy-pasted the cells with data validation from a quite older back-up version of the file that I had stored in the old PC (which was never copied to the "new" PC). It is fortunate that I had not changed the architecture of the file at all, so the sheet names + cell numbers were accurate. To make sure I minimize intervention and I don't lose any existing information, I only pasted the data validation cells where I would add new entries - so the old entries are not linked to my lists anymore.

    I could now see the validation under "Data" / "Data validation", but not the drop-down menu buttons, so I applied nwidnsor 's advice and now it seems to be working fine. Thanks!
    Last edited by nas_k; 01-16-2021 at 07:43 AM.

  12. #12
    Registered User
    Join Date
    03-14-2021
    Location
    Honolulu, HI
    MS-Off Ver
    Office 365 (Mac)
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    Thank you for this! I had to update an old yet very complex sheet and ran into this disappearing dropdown issue. I tried the 'show all' (or the reasonably Mac-flavored equivalent) to no avail. So I deleted all of the buttons from that page and suddenly all of the dropdowns reappeared. I recreated the buttons and away I went.

    Also interesting to note that all old buttons had been converted to images as they got rid of ActiveX or something like that?

    In any case, thank you to achildofearth and everyone else who helped with this strange and frustrating issue. Would love to hear what is causing this so that I can work to avoid it in the future without having to delete/recreate the buttons. Still, easier to do that than to copy/recreate this monster!

  13. #13
    Registered User
    Join Date
    06-14-2014
    Posts
    1

    Re: Data Validation Drop Down list disappeared

    I've been having the same problem with drop-downs suddenly disappearing.
    Dropdown.jpg
    This is what I have to do on a repetitive basis. Re-Check the "in-cell dropdown" to re-enable it.

    I will give nwindsors solution a try.
    Thanks

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,387

    Re: Data Validation Drop Down list disappeared

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1