Filter Google Spreadsheet Array Formula

Question

I have a spreadsheet with 2 columns. B1 contains a custom function that returns an array that overflows to fill the column. I want to create a few filter views that each show all entries with 1 given value in column B, and sorts by column A. The original data can't be presorted. I can get the filter to show only the specified value from B, but once I try to sort by column A, it breaks and no longer shows the contents of B, except for Loading... in the cell the formula was placed in.

I saw some info on some CONTINUE function, which looked like it could fix my problem, but unfortunately that function was removed in the new Google Spreadsheets. Is there an alternative to CONTINUE in the new Google Spreadsheets or some other way I can do this?

Answer 1

I believe the issue is caused by filtered view sorting the column that contains input to a function. It can be reproduced in the following minimal example:

+---+-------+---+
|   |   A   | B |
+---+-------+---+
| 1 | input | 1 |
| 2 | 5     |   |
| 3 | 4     |   |
+---+-------+---+

where B1 is the formula =A2-A3. Create a filtered view for cells A1:B3 and use it to sort column A in ascending order. The result: B1 will display #REF error

The formula in cell A2 is referencing a range in the active filter that is not in the same row.

I can't really explain why this fails: my guess is that the logic of filtered view does not handle remapping cell references within the view.

But I can suggest an alternative approach: use FILTER and SORT, possibly putting results on different sheets for ease of sharing or reading. For example,

=SORT(FILTER(A2:A15, B2:B15=5), 1, TRUE)

keeps only the entries from A with 5 in column B, and presents them in ascending order. This formula could appear in another column (C,...), or on a different sheet.