# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Data Validation Drop Down list disappeared

## Mile029

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.

----------


## davesexcel

Hi,
try going to data validation and see if in cell dropdown is unchecked

----------


## Mile029

I have it clicked.  don't know what the problem is.  I have everything setup to have data validation lists in the spreadsheet, but the list are not appearing in a drop down in the cell.

----------


## Mile029

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.

----------


## davesexcel

> 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.



Hi there,
Not sure what you mean,
I am guessing the list is on a different sheet than the data validation, if this is the  case then you can name the range that the list is located, then in data validation source enter
=namedRange

----------


## Mile029

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.

----------


## Taemex

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.

----------


## nwindsor

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:"

----------


## qandida

YES!!!!!!! the post above is right on!

----------


## ballasa

I know this old, but this just saved me an extremely large headache with a big model I've built.  The dropdowns on every sheet just stopped showing up.  

Thank you, nwindsor!

----------


## nwindsor

Haha no sweat!! I love that after so long the post is still helping people! 
Nothing more frustrating than an excel problem you can't solve  :Smilie:

----------


## DanOinLA

O.K.  I am having the same issue with Excel 2013.  The Data Validation I use is an Offset function to allow for a "Smart Drop-Down" (Allows you to enter the first few characters, then click the drop-down list arrow to get a filtered list).  DV formula is "=OFFSET(Resource_Info,MATCH(LEFT(V56,LEN(V56)),LEFT(ResrcList,LEN(V56)),0),0,SUMPRODUCT(--(LEFT(ResrcList,LEN(V56))=V56)),1)."

I have a similar formula in two different areas of my spreadsheet.  I have tried every other suggestion I have found.  Nothing works.  When I open the file the Data Validation is completely gone.  For now I have written a quick macro and assigned it to a button that goes through each cell and "recreates" the DV.  

Would love a better solution.  Any suggestions?

----------


## DanOinLA

Should probably add to my post that "normal" Data Validation entries (a.k.a. simple lists) do not have the same issue.  They all work fine after multiple file close and re-open steps. The problem is exclusive to the Offset function Data Validation used to create Smart Drop-Downs..

----------


## mcclausky

> Should probably add to my post that "normal" Data Validation entries (a.k.a. simple lists) do not have the same issue.  They all work fine after multiple file close and re-open steps. The problem is exclusive to the Offset function Data Validation used to create Smart Drop-Downs..



Has anyone been able to resolve this problem?  :Frown:    I'm having exactly the same problem described by DanOinLA:  Data Validation with OFFSET functions used in DropDownLists.

Your help will be truly appreciated.

----------


## mpenn

> Haha no sweat!! I love that after so long the post is still helping people! 
> Nothing more frustrating than an excel problem you can't solve



Still helping in 2016; thanks!  (Worked like a charm!)

----------


## DaveU

Posted this in error, can't figure out how to delete it.

----------


## DaveU

> 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:"



Sorry, but this doesn't always seem to apply.  My option "ALL" was already checked, tried toggling it to see if that would do it, but no luck.  The only thing that worked for me was copying it to a new sheet.

----------


## protonLeah

DaveU,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## nayanexcel

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

life now easy.

thanks.

----------


## sureshkss

I need the formula for Reset value in dependent cell when another cell is updated

----------


## achildofearth

I have found a solution tho it may be only a solution to something really specific. My drop down would disappear and I could not find anything on the web that would solve my issue. So I took matters into my own hands and did some experimenting on my workbook. I started deleting things; tabs, images, ect.. 

My drop downs came back after deleting an image. It seems that the images are somehow interfering with the rendering of the drop down arrow image. Or maybe a memory issue, too much memory being used.

If that doesn't help try saving and renaming you file as "TEST" and do some experimenting for you self and try deleting things to see if it comes back

----------


## achildofearth

I have just found that "Freezing Panes" will cause the drop down to reappear. I know that it sounds weird but it works for me. I don't need any panes frozen so I just inserted a new row 1, froze it, and hid it and now I do not have any problems with my drop downs.
I hope this helps someone.

----------


## Lanfir

> 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  :Smilie:  Thanks dude!

----------


## FDibbins

Thanks for the feedback always appreciated  :Smilie:

----------


## smacdates

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?

----------


## krushy

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!

----------


## Jared Whitener

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.

----------


## nas_k

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!

----------


## dhartenstein

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!

----------

