Data Validation not working on existing workbook/can't create new

Question

I opened a workbook from Windows with some cells with data validation on my Mac. The pulldown doesn't.

I tried to edit the validation. Got an error. "This can't be applied to the selected range. Select a single cell in a range and try again."

I tried to create a new workbook with a cell for data validation. Same problem.

Some screenshots:

Any thoughts?

Answer 1

I get the same when I press that button (i.e Remove Duplicates). But that's not the one for Data Validation. See picture below for the correct one.

Answer 2

Well, don't I feel semi-dumb.

The semi- thing is, my pulldown from Windows still doesn't work. It shows the down arrow, I click the down arrow, nothing pulls down. But, at least I can "edit" it.

Among other weird things, it says the range source is "=_xlfn.ANCHORARRAY($B$254)" not the =$B$254# it says in Windows. So, a) I have no clue what the _xlfn.ANCHORARRAY() is/does/means, and b) apparently, the range# hashtag doesn't work as a row source when defining/using a validation list on Excel for Mac. It also doesn't appear to work as a cell formula, e.g., =myDynArr#.

Sigh.

No wonder I still do all my Excel work on the Windows machines not my Macs; I run into this kind of stuff every time I try to. But it also means that my most-frequently-used spreadsheets are unusable on Excel for Mac. By now they are littered with all the new-fangled cool stuff like dynamic array and associated functions, trimrange, LAMBDA(), the regex functions, etc.

Answer 3

The xlfn bit means that whatever follows is not supported. In this case, the # operator that dynamically selects a spilt array doesn't work. You must be using an older Excel version on your Macs. If you would upgrade to MS365 on your Macs you'll get all the cool new features, be it that Excel for the Mac is still missing some features in comparison with the PC version. Like full functionality in Power Query, and Power Pivot / Data model isn't supported at all. Just to mention two major differences.

Answer 4

Really? I’m signed in and have a paid-up MS365 Family license. It updates regularly from Apple App Store. Are the Apple App Store installs and updates not “good enough” for current MS365 features, even if still not as featured as “real Office” MS365 on Windows? Do I need to install/run from an Office.com installer, not the Apple App Store distribution?

Edit to add: see screenshot above for License: Microsoft 365 Subscription.

Edit to add 2: this Mac is only three months old. Was the Office on App Store even three months back not compatible with dynamic array, which has been in Excel for MS365 Windows for years at this point?

Answer 5

Can’t tell why it doesn’t work for you. I have MS365 and have access till all modern dynamic array features on all my Macs.