Excel o365 Drop down validation lists not showing

Question

I am using Excel 16.0.13231.29372 64bit on a windows 10 machine.

I downloaded an excel worksheet that should be showing a drop down list for validation but it is not. I thought maybe the sheet was corrupt so I created a new workbook

I create the name with 4 cells so I should get A,B,C,D in the drop down

I created the field validation next making sure to enable the in-cell drop down

It then shows the drop down error but none of the options. It does still limit my input to a,b,c or d though.

Also I have checked this setting and made sure it was set correctly

Any suggestions or ideas?

Answer 1

Does the workbook contain any VBA code? If so, if any VBA code is run to delete all shapes on a worksheet then problems occur trying to create Data Validation DropDowns on the worksheet. Sometimes they will not display the list and sometimes will not even display the DropDown arrow. However, entries in the cell that are not included in the List are still rejected.

See the last couple of posts at the following link.

https://www.ozgrid.com/forum/index.php?thread/64488-data-validation-drop-down-not-showing/ 

I have tested and I can reproduce the problem.

I found that I can create the DropDowns on other sheets within the same workbook but not on the same sheet where I have deleted all shapes.

After saving the workbook and closing Excel and then restart Excel and open the workbook, the Validation DropDowns all worked again.