Forum Discussion
=FILTER function and cells: empty array error?
FrancoiseM 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.
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&rlkey=nlmmfgd57fhk8lg8heztbjizh
Thank you !
FrancoiseM 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.
- 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.
FrancoiseM Note that you used references to Tutorials[All#]. Just Tutorials will do. 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.