=filter formula

New Contributor
Hi everyone,

I am having issues with my =filter formula. It works fine on my computer, but when colleagues open the worksheet and start working on it, the filter doesn’t flow through on different sheets. Then I get the “can’t change part of an array” error. When I change all the formulas to work and sent it out again, the same thing keeps happening
5 Replies

@Renae225 

 

FILTER function

Note: This function is currently available only to Microsoft 365 subscribers.

 

Thank you, so if someone who didn’t have 365 opened it, would it break the array when I opened it again in 365? I’m finding it’s broken for me too after they open it
Also, is there another formula that would do the same thing, and work in older versions of excel?

@Renae225 In the older Excel versions 2007 or 2012 you can find the function

via Data -> Sort and Filter “-> Filter

In current versions such as Microsoft 365 or Office 2019, however, the filter function can be found in the Start menu in the "Edit" area.

 

But no matter which Excel you use. In all cases you can use sums, averages or other functions in a filtered list. Excel then generates results that depend on which data records are currently visible in a list.

There are many ways that lead to Rome and there are many more ways that can lead to a suggested solution in Excel.

 

Maybe with ... Dynamically filter Excel lists by function - AGGREGATE and FIND

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

@Renae225 

“can’t change part of an array” means you try to change the cell inside the spill returned by FILTER(). You may change only entire formula in top left cell of the spill.