Forum Discussion
Data validation dropdown list isn't working
srugh I can't say that I have. One possible cause is that objects have been hidden for the entire workbook. Go to File > Options > Advanced, then scroll down to "Display options for this workbook:" and make sure "All" is selected under "For objects, show:".
Advanced Options
It's also a good idea to double-check the data validation rules to make sure "List" has been selected and the "In-cell dropdown" checkbox has been checked.
Data Validation List
If indeed the settings are correct, try clearing the data validation rules and recreating them from scratch.
Lastly, if Freeze Panes has been applied to the neighboring column (on the right) and the horizontal scroll position is too far to the right, the drop-down arrow may appear to be missing when all you need to do is scroll back over to the left.
Freeze Panes: Column D hidden by horizontal scroll position
If none of these suggestions apply to your situation, it's possible the workbook has become corrupted. You could try restoring the file from a backup (if available) to see if the problem exists in a prior version of the file; or, try recreating the data validation rules in a new worksheet to see if the problem persists.
Kind regards.
To add input, I had the same issue which occurred suddenly, without any previous changes having been made. I tried the mentioned solutions and unfortunately, none of them worked for me. However, what did fix the issue was adding the command 'Data Validation'! before the list values in the "Source" input box.
Sometimes it will be automatically input when you are applying the data validation, but not always. If it's not, then you will have to type it out, which was the case for some of my columns. I am using the online version of Excel, but this should still apply for the desktop app, as the error was occurring regardless of version used.