Data Validation drop down arrow disappeared Excel for Mac

Question

Split from this thread.

Tisky

i am following this discussion with interest.

i have exactly the same issue with an .xlsm file that was created on a pre 16.17 version of Office for Mac 2016.  I created the file on version 16.11

it has been working fine up until 16.17, and now the arrows have dissappeared.  The dropdowns are there if you hover cursor at bottom right of cell.  I created the data validation box in normal manner, and these all work on a Windows Machine with very latest Office updates installed.  And work on Excel 2016 for Mac version 16.16

so i simply cannot see how this is in any way related to how i created the dropdown

it is surely a code fault in the update for Excel to 16.17

i have just tried rebooting in

safe mode and still no change.

Using Excel for Mac Version 16.16

i have created a spreadsheet with Data Validation cells and saved as .xlsx, and .xlsm files

Then trialled it on 16.17, and dropdown arrows are present.

So it seems to be a longevity issue with data validation cells created pre 16.16 version

Safe Mode and still no change

Answer 1

Hi. I'm having this problem too, with Excel v. 16.20 and most earlier versions going back to 15.nn. When I first created the sheet a couple years ago, it worked perfectly.  Data validation drop down menus worked fine, so I have several sheets in a workbook where I have such data validation. It seems to me I began to have this problem in the last year or so.

Anyway, I have fixed it. 

The sheet in question is a multi-column table. Three of the columns have data validation using dropdown menus. That validation method which originally worked perfectly in Excel 2011 and I think in early versions of 15.nn wasn't working (no arrows) in any of the three columns. After I copied everything into a new sheet following other advice in this thread, two of the columns were fixed, the arrow appeared in any selected cell. The third column, which previously also showed no arrow, now showed an arrow which didn't react and produce a drop down menu when clicked. So the arrows were present in the third column but were not functioning.

I looked at the data for the dropdown in the validation dialog. It consists of two items: ✓ (a checkmark) and a blank space. Without going into detail about the reasons for this, the blank space was the culprit. If I remove it from this column's dropdown, the dropdown works correctly. Other fiddling seems to show that, in effect, you cannot use a blank space as a choice in a dropdown menu (even though in the past it worked fine with a blank space).

I substituted a character other than a blank space so that the user could remove a checkmark. A workable but not perfect solution. The third column dropdown now works.

Hope this helps.

Answer 2

I will share one of the sheets with you, which has a dropdown.  The dropdown is in the fuel cells

Answer 3

Hi Banjo,

To give you a further support, please share with me a copy of your workbook template to let me help you. To protect your privacy, please share with me your file via Private Message.

Thanks,

Neo

Answer 4

Neo, many thanks for your prompt response

i know that i can create new workbook with new worksheets in version 16.16 and the dropdowns will work and show in 16.17 on a Mac - i again reiterate, all functionslity is still in place using a Windows platform now!

But

the complex spreadsheets (with macros and linked formulae etc) are templates that upwards of 50 colleagues also personalise and use....  

i had to issue an updated version at the 16.11 to 16.12 update for Excel 2016 for Mac (because functionality was lost on update - of note, no functionality was lost with the same Workbook on a windows operating system)

i cannot keep creating a new template, and transferring all data at random updates, because the rollout fails to ensure functionality of any legacy workbooks.

the issues is that there seems little longevity of files that are only 6 months old.

Answer 5

Hi Banjo,

Based on my test, I create a new workbook with a Data Validation list via Excel 16.16 and the drop-down arrow displays well when I select the cell. It works with .xlsx and .xlsm file format under Excel 16.16 and 16.17. 

To check if this issue is related to the old file, I'd like to suggest you create a new workbook with Data Validation list via Excel 16.17. If the drop-down arrow displays well, I'd like to suggest you copy all your data from the issue problematic one file into the newly created workbook. For your reference: Move or copy worksheets or worksheet data.

Please have a try and share with me your result.

Thanks,

Neo