7

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?

3

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.

  • This doesn't seem to work for a filter view - where do you enter it in? – Michael Sep 23 '14 at 16:36
  • 1
    I see. If you're saying it's not at all possible to do what I'd like to do in a filter view, I'd suggest adding that information to the answer so I can accept it. Thanks. – Michael Sep 23 '14 at 21:24
  • 2
    I'll put my neck on the block: 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. – AdamL Sep 24 '14 at 1:49
  • @pnuts, I will do, but not before giving you the opportunity to edit your own answer - IMHO the thread would look cleaner doing it that way. – AdamL Sep 24 '14 at 23:27
  • Do you put this query in the filter or the datatable? I'm no confused... looking for hours... big fan of google sheets but excel is better at this.. – Happy Bird Aug 30 '18 at 19:09
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

filter by custom formula

=(A2=$C$1)

for your specific question you would use

=A2>500
1

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.

0

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.

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.