Deleting A Dropdown Menu

[Rookie06]

Hey. I was editing a file recently and wanted to include a dropdown menu for other people to open and be able to choose some options. When I reopened the file later, clicking on the dropdown menu did nothing, and it was impossible to delete. It just hovers there now and this file is so far in development now that I can't simply restart it. It looks like this

I would like to know how to get rid of it and perhaps prevent something like this from happening again. Thanks in advance.

 

[Yongle]

How was the dropdown created?
How have you tried to delete it?
Is this problem confined to one cell?

 

[Rookie06]

How was the dropdown created?
How have you tried to delete it?
Is this problem confined to one cell?

I was on a cell, went to Data Validation, selected Lists, then highlighted 6 cells on a different tab. It worked initially, and I dragged it down to the 6 cells below and they all worked and I could choose an option. Then I saved and quit out, and when I came back later, there was only one dropdown (the one in the picture) and I was unable to click it or delete it.

 

[Yongle]

I have experienced similar "ghosts" - including with DV and if I remember correctly it was due to a Microsoft "issue" (Excel 2010 I think)
From your reply it sounds like you did not originally remove the data validation - it happened "somehow" when the workbook was closed/re-opened
Assuming that you did not delete the other tab, it sounds like a minor corruption.
Personally I would "start again" with the workbook to avoid the risk of being "bitten in the bottom" later when the workbook contains important data ( these things always happen at the most inconvenient time )

But one of these may work
- delete the 6 rows and the whole column \ insert a new column
- right click on sheet tab \ Move or Copy \ Copy the sheet \ Delete the original sheet \ Rename the new sheet

 

[Rookie06]

I have experienced similar "ghosts" - including with DV and if I remember correctly it was due to a Microsoft "issue" (Excel 2010 I think)
From your reply it sounds like you did not originally remove the data validation - it happened "somehow" when the workbook was closed/re-opened
Assuming that you did not delete the other tab, it sounds like a minor corruption.
Personally I would "start again" with the workbook to avoid the risk of being "bitten in the bottom" later when the workbook contains important data ( these things always happen at the most inconvenient time )

But one of these may work
- delete the 6 rows and the whole column \ insert a new column
- right click on sheet tab \ Move or Copy \ Copy the sheet \ Delete the original sheet \ Rename the new sheet

Unfortunately, I had already restarted this because of the issue at the beginning. It emerged again after completing the file. It seemed that copying worked. I get the feeling it'll come back unfortunately. If I make a list over it again, it seems to work as well.

Here's the problem. Whenever I save and quit, the drop downs disappear. If I save with the cell selected being one that has a drop down, the drop down effectively disappears but the icon shows up still and can't be deleted. So while copying got rid of it, it seems that I cannot save dropdowns for some reason and it's a bit bothersome

 

[Rookie06]

Interestingly enough, it seems that if my dropdown list is on the same tab as where I get my list elements from, it saves. This is really kind of weird. Is there anyway to get around this? I may just stick to this anyway even if it's less convenient, because the sheet works I suppose.

 

[Yongle]

Try testing with a new workbook and 2 tabs.
If it continues to do the same thing I would uninstall and re-install Excel
A possible workaround would be to create a named range for the items in the list and refer to that in data validation
(I have no reason to think it will work, but it is worth a try!)

 

[Yongle]

A quick trawl on Google highlighted several examples of this very issue which happened when Excel 2010 users saved workbook as xls
The reason given was that in earlier versions of Excel the list had to be in the same worksheet as the cell with DV

Is that what you are doing?

The solution suggested was to save the file as xlsx

 

[Rookie06]

A quick trawl on Google highlighted several examples of this very issue which happened when Excel 2010 users saved workbook as xls
The reason given was that in earlier versions of Excel the list had to be in the same worksheet as the cell with DV

Is that what you are doing?

The solution suggested was to save the file as xlsx

The file has been passed around since 2009 and improved / adjusted since then. It was saved as a .xls and I just now changed it to a .xlsx. It seems to be working now. Thank you!

 

[Yongle]

Glad it worked - Google has its uses! Thanks for the feedback