Google Sheets – Confused by ArrayFormula(IF+FILTER+IF) Function

If your data is in A1:D10, maybe:

=filter(A1:D10,A1:A10>500)

To allow for "new ones will be added" put the formula outside A:D and adjust to:

=filter(A1:D,A1:A>500)  

Edit to include comment from someone who knows (@AdamL):

it is not possible to do what you want with the Sheets filter tool alone. You can combine both the filter tool and a FILTER or QUERY formula (eg apply the filter tool to the output of pnuts' solution, which could also reference the 500 part in another cell, perhaps in the header row to the side). Alternatively, a helper column(s) could be used to simulate the required functionality.

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.