Menu
I'd like to use ArrayFormula to populate a column in spreadsheet, but when I Sort A->Z in a filter view, the ArrayFormula column vanishes. In some cases, the column includes a #REF! error about the range, and in some cases the column is just blank after the Sort. The following is a simplified version of what I'm trying to do (in my actual application, I'm doing a Vlookup to another sheet):
https://docs.google.com/spreadsheets/d/1XbqqedOjuSKuE-ZLIHNw59-r01EsNMpx7YVqOoxSOR4/edit?usp=sharing
The column 3 header uses an ArrayFormula to copy from column 1. If you go to the Filter 1 filter view, you'll note that column 3 is blank except for an error. This happens after I try to Sort Z->A on column 2. In my more complicated use-case, involving a Vlookup, after a Sort the column disappears entirely (leaving no #REF! error). Before sorting in both cases, everything is fine.
How do I make ArrayFormula values persist in filter views after sorting?
Thanks for your help!
735
Inside Filter, there is no need to use the ArrayFormula function. You may have noticed the formula in cell E2. As you can see, the formula returns an array result. In the Filter function in Google Sheets, the column that contains the condition can also be outside the filter range.
That means to avoid sorting messing up array formula, enter the array formula in the header row that contains column names. But you can't use the formula as it is. You may need to make some minor changes in the formula as per the below syntax. That means you should add a column name with the array formula.
If the Sort Range option is greyed out it means you haven't selected a range. Check you have a range highlighted as shown above where I've selected cells A1 to B4. Once you click the Advanced sorting range options you'll see a modal window that will help to determine how you would like to sort your data.
Array formula normally breaks if it resides in any cell within the range we sort. Can we stop array formula messing up in sorting in Google Sheets? In most of the cases, we can stop array formulas messing up in sorting. The simple solution is moving the array formula one row above the sort range. I will explain how to tune the formula for that.
How To Use The ARRAYFORMULA in Google Sheets In order to work with it, you need to change the single cell references in the original function. Instead of cell references, you would have to pass column or row references. As an example, let’s see how can you use an ARRAYFORMULA to copy a formula down an entire column.
Here is one example of an array formula that breaks in sorting in Google Sheets. As you can see there is an array formula in cell C2 which returns the grades of students based on their scores. It’s a nested IF array formula that works as below (not a formula explanation).
Array formula normally breaks if it resides in any cell within the range we sort. Can we stop array formula messing up in sorting in Google Sheets?
I'm guessing that, because your references are normal (relative, not anchored/absolute), the range A2:A10 after sorting down turns into something absurd, like A7:A4, depending on actual sorted values.
Also, if you hover with your mouse on the #REF error, what does it tell you?
Anyway, try using absolute references in your formula:
=arrayformula({"Column 3"; A$2:A$10})
Edit Fascinating. It's the first time I see this type of error. Taking it at face value, it seems that it's a limitation of Google Spreadsheets - you cannot use ARRAYFORMULAS spanning multiple rows inside sorted filter views, because, like I sort of guessed, it messes up the ARRAYFORMULA's range (as indicated by the fact that the formula is now in C4 instead of C1).
But that gives you also the solution: do not include the cell with the arrayformula in the filter view. Instead of making your filter view's target range A1:C20, make it A1:B20. Then the arrayformula in C1 will be untouched by the filter and will indeed continue to work.
81
I have found a solution for my usecase, in your case, it could be:
=arrayformula(if(row(C:C)=1;"Column 3";A:A))
But you'll need to consider the whole columns in your formulas. Example
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
