Forum Discussion

FrancoiseM's avatar
FrancoiseM
Copper Contributor
Jun 22, 2023

=FILTER function and cells: empty array error?

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 th...
Excel on Mac
Formulas and Functions
FrancoiseM's avatar
FrancoiseM
Copper Contributor
Jun 22, 2023
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's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 22, 2023

    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.

    • FrancoiseM's avatar
      FrancoiseM
      Copper Contributor
      Jun 22, 2023

      Riny_van_Eekelen ok that would make sense, I’ll work on getting things in the right format and hopefully that’ll do the trick. The gazillions of extra rows come with the original file but I will copy/paste the relevant rows instead. Thanks a lot for your help!

Resources