=FILTER function and cells: empty array error?

Question

I'm having some issues with the =FILTER function on Excel MAC version (16.74). A really annoying one is that it does not work with cells (like A2 instead of a word or a number). It comes back with the CALC! error every time (empty array). That includes drop-down cells which makes filtering a bit clunky. there's no hidden cells/rows or whatnots, it's not a format problem neither. Any advice from MAC users appreciated!

riny_van_eekelen · Answer

FrancoiseM&nbsp;The FILTER function works exactly the same on a Mac as on a PC. So, can you show exactly how you are using the function? A screenshot showing the formula and the cells/ranges it references. Or better to share a link to a file on Onedrive or something similar, demonstrating the issue.

francoisem · Answer

Riny_van_Eekelen&nbsp;I realised it was not a MAC issue after sending this message and trying our online 365 version, same problem which is reassuring in a way. An annotated version of the file is at:https://www.dropbox.com/scl/fi/el7r5j3f0uzu8hijrtbgj/for-correction-copy-2.xlsx?dl=0&amp;rlkey=nlmmfgd57fhk8lg8heztbjizh&nbsp;Thank you !

riny_van_eekelen · Answer

FrancoiseM&nbsp;See attached. Google on "structured table references in Excel" to learn more about them and see if you can apply 'my' formulas to you real life situation.

francoisem · Answer

Thanks for that, it clearly works on your spreadsheet but when I copy the formula to my real life document, changing only the cell "coordinates" (manually or by clicking on it) it still doesn't work and comes back with CALC error message. Maybe something is corrupted somewhere (there are several tabs, and other data being collected on the dashboard...) I think I'll have to start from scratch and rebuild the document bit by bit.

riny_van_eekelen · Answer

FrancoiseM&nbsp;Note that you used references to Tutorials[All#]. Just Tutorials will do.&nbsp;Then all the Blocks in the tutorial table were texts (I changed them to numbers in my version), whereas the references in column A on the Dashboard are numbers. So, your file contained a mix of issues that caused the various error messages.Consistency is key. Block references are either texts or numbers but you can't mix them. And use proper structured references. Furthermore, the first row in the Tutorial table shouldn't be there either. What is it's purpose? And having thousands of empty rows at the bottom of a table isn't very meaningful either.

Forum Discussion

=FILTER function and cells: empty array error?

6 Replies

Resources