Dropdown lists not working for Mac user

[kyle4570]

With the attached, a handful out of hundreds of users were not able to use the dropdown lists demonstrated in Cells A15 & B15 of the "Data Entry" sheet. The dropdowns were completely unresponsive when clicking the arrow.

For most of them, it was discovered that they were using Office 2016 and that after upgrading, the dropdowns worked as expected. I guess that means that some of the formulas/functions present in the "Branch DD" and "Word DD" sheets (which feed the dropdowns) were unsupported in Office 2016.

The dropdown in Cell A15 of the "Data Entry" sheet (fed by "Branch List" & "Branch DD" sheets) allows the user to type in part of a Branch Name and then click the arrow to narrow down the dropdown results.

The dropdown in Cell B15 of the "Data Entry" sheet allows the user, after selecting a Branch, to only see the Words from the "Word DD" sheet (fed by "Word List" sheet) that belong to their Branch.

So, after the Office upgrades helped most, there remains a user for which the dropdowns still don't work. One difference is that she is a Mac user (High Sierra 10.13.6.) I have verified her Excel version as Microsoft Excel for Mac, Version 16.43 (20110804), which seems to be a 2020 version.

Anyone have an explanation? This is very frustrating. It's not as if dropdown lists are the most sophisticated thing in the Excel domain.

[6StringJazzer]

Why use Branch DD as the source for the Branch list, when that data is already on Branch List? I think the TRANSPOSE function is biting you here. Prior to Office 365 I think this needed to be used in an array formula. In 365 it automatically becomes an array formula. Similar for Word DD.

I don't see the purpose of Branch DD. For the Branch data validation list I would use

Please Login or Register  to view this content.

On Word DD try entering the formula explicitly as an array formula. Select A1, then F2, then CTRL+SHIFT+ENTER.

[kyle4570]

Branch DD is in use and is the way it is so users can do a word match and reduce the list of almost 200 dropdown choices.

When I do the array formula as you mention in Word DD, only one word gets matched to the Branch and appears in the dropdown.

[janmorris]

i am just replying to confirm the issue.

i have macOS Big Sur (latest version updated tonight), and MS Excel for MAc v 16.35 (20030802) which has not been updated in quite some some time.

the dropdowns are not working for me.

Branch appears to be using the following formula:

Please Login or Register  to view this content.

Word appears to be using the following formula:

Please Login or Register  to view this content.

the suggested formula provided by 6StringJazzer works for me.

if there are so many branches, and the idea is to narrow down choices, then perhaps adding another (first initial) dropdown for states could help to lessen the amount shown.

[6StringJazzer]

There is no function ANCHORARRAY in Excel. I suspect this is an indication that the "#" operator is unsupported in the Mac version of Excel. The "#" operator indicates that the data source is a spill range. Spills are a new thing in Office 365 and I believe are not available in any prior version. This file makes liberal use of them to generate both lists.

The only way to make this backwards compatible is to eliminate spill formulas.

Branch DD is in use and is the way it is so users can do a word match and reduce the list of almost 200 dropdown choices.

I don't really follow what this means. The horizontal list in Branch DD is exactly the same as the vertical list in Branch List, but in alphabetical order. I don't see what you are doing with Branch DD that you can't do with Branch List.

[kyle4570]

That =_xlfn.ANCHORARRAY is what Excel sticks in there if it is an unsupported function.

The actual formula on my end is

Please Login or Register  to view this content.

I've asked the user to check the Data Validation formula on her end to see if it has the transformation similar to yours. As mentioned, she's on a 2020 version of Excel. I don't believe the formula above or the formula where it points to (below) includes anything new enough not to be supported. It makes no sense to me.

Please Login or Register  to view this content.

[kyle4570]

Spills are a new thing in Office 365 and I believe are not available in any prior version. This file makes liberal use of them to generate both lists.

The only way to make this backwards compatible is to eliminate spill formulas.

So, just to clarify: Spills were introduced to Excel after the (alleged) November 10, 2020 release date for Microsoft Excel for Mac, Version 16.43 (20110804), which is what the user has installed?

[janmorris]

if the user has the latest version of excel for macos, then it would seem that spills were NOT introduced in the latest update for excel for macos.

[6StringJazzer]

That update 16.43 can be applied to either Office 2019 for Mac or Office 365, and I can't tell just from the update what version is installed.

Spill formulas were not introduced to any version of Excel prior to Office 365, even though there is overlap in time as to what versions are offered.

Office for Mac that comes with a Microsoft 365 subscription is updated on a regular basis to provide new features, security updates, and non-security updates.[...]Starting with the 16.17 release in September 2018, these release notes also apply to Office 2019 for Mac, which is a version of Office for Mac that’s available as a one-time purchase from a retail store or through a volume licensing agreement. But, some features listed in the release notes are only available if you have a Microsoft 365 subscription.

These releases are mostly bug fix and security patches; they don't go back and add new features to Excel 2019, and I'll bet you a pint (anything but Coors) that is what your user has.

[6StringJazzer]

if the user has the latest version of excel for macos, then it would seem that spills were NOT introduced in the latest update for excel for macos.

The user does not have the latest version of Excel; the user has a November 2020 update to whatever version they have. That is not the same as having Office 365.

[kyle4570]

Does the attached screenshot clarify anything?

[6StringJazzer]

I guess I owe you a pint. It does confirm that the user has Microsoft 365, but it invites the question as to why it is not the latest update (August 10, 2021, 16.52).

Let's go back to your first post:

dropdowns still don't work.

What does this mean? What happens? What do the sheets Branch DD and Word DD look like? Do any formulas produce errors? Do you get the spill cells? Or do you get something like what janmorris described (_xlfn.ANCHORARRAY)?

[kyle4570]

Perhaps MacOS High Sierra 10.13.6 only updates to a certain point before it no longer meets minimum requirements?

Dropdowns don't work as in totally non-responsive. As if it's a normal blank cell. Exactly what was occurring with the many who updated their Office installs and solved the problem.

So, I need to investigate further on how/if she really did an upgrade attempt like she said she did, and also I have asked her to send me Data Validation settings so I can see if the ANCHOARRAY effect is in play.

[janmorris]

ask the user if they can check what the formula looks like in "Data Validation"

screenshot attached (for my version of Excel on macOS)

Attachment 744741

[6StringJazzer]

screenshot attached

Invalid attachment. Sometimes image attachments fail. I have found that it is effective to start the post then Go Advanced first before attaching the image.

[janmorris]

thanks for the tip, i will do that in future.

NEW screenshot attached (for my version of Excel on macOS)

Screen Shot 2021-08-20 at 8.48.34 pm.png

[kyle4570]

Ancho array effect not happening to her. Unbelievable

[6StringJazzer]

Hard to get much farther without hands-on diagnosis. Will your user allow you to do a screen share to dive into it?

[kyle4570]

Somehow, on her own she decided to upgrade the OS on her machine from High Sierra to Big Sur. Weird that she could make that many jumps on the same machine, but good news: IT WORKED. No more dropdown problems. So, in the end, it was a combination of an Office upgrade AND a Mac OS upgrade. Silliness.

[6StringJazzer]

Well, that's a weird but happy ending. Kind of like getting a tune-up and the rattle in the trunk goes away.

[kyle4570]

Thanks all for helping me troubleshoot.

janmorris - added some rep for you.

6StringJazzer - I got the "You must spread some Reputation around before giving it to 6StringJazzer again." response. Next time!