2

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?

  • Does the function in B1 use column A as the input? – user79865 Aug 5 '15 at 4:59
  • Also, does the range which you sort include the cell with your custom function? Does this cell get moved to another position as a result? – user79865 Aug 5 '15 at 5:17
  • Yes, yes, and yes, but I could easily move the function to display the header and sort only the cells that don't contain the function. – DeadEli Aug 5 '15 at 5:44
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.