CODEWITHSUNDEEP
sortingfiltergoogle-sheetsarray-formulas
snl
snl

Reputation: 608

ArrayFormula column disappears when sorting in a filter view in Google Sheets

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 arrayformula use bad filter view

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!

Upvotes: 9

Views: 5017

Answers (3)

Joan Miquel
Joan Miquel

Reputation: 1

Have you tried A2:A? If you don't put an ending row, means the end of the column. It worked for me.

Cheers

Upvotes: 0

Capitaine XLR
Capitaine XLR

Reputation: 66

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

Upvotes: 5

ttarchala
ttarchala

Reputation: 4567

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. enter image description here

Upvotes: 7

Related Questions