Is there any way to use a formula in a Google Sheets filter view?

Question

I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, and new ones will be added, so manually checking off values in the filter isn't really feasible.

My searching so far hasn't turned up anything, but I haven't seen anyone say that it's not possible. If it's not, can you suggest an alternative approach?

Answer 1

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.

Answer 2

Remember to us the cell under the filter heading in the formula, so if your filter is on A3, use A4. Here is an example for a filter in cell A1, checking if it matches a value in C1

=(A2=$C$1)

for your specific question you would use

=A2>500

Answer 3

In filter you choose filter by condition, then custom formula is

=(O:O >date(2016,1,1)) + (O:O ="")

The letter is the column letter. The + is use to do multiple conditions.

(OR)

Best practice is to test them solo. For example just write

=(O:O >date(2016,1,1))

to make sure that part of the formula works. Then test the other one alone and so forth. When you have tested all of them just put them all together with a + between each and voila.

Answer 4

I solved the problem as follows:

1. Create a Filter (not a filter view).
2. In the Filter View submenu (or Filter toolbar drop-down), choose "Save as Filter View".
3. In the Filter View submenu (or Filter toolbar drop-down), select "none".
4. Remove the filter.

The end result is a filter view with the conditional filter applied, but no filter on the original data.