Filtering in numbers

You are not missing anything. You can set up only one filter at a time. If you want to filter your table a different way, you make changes to the filter (which overwrites the one you had previously).

However, you can make numerous filters (or "dynamic" filters) with a few columns and a single actual filter. Below is an example put together for someone who wanted to filter their table of music. You enter the filtering criteria into a small table. The main table has extra columns containing formulas that determine which rows meet each criteria. One final column puts it all together and you put a single "if true" filter on that column.

Column D is TRUE if the name matches

Column E is TRUE if the year range matches

Column F is TRUE if D and E are TRUE

Filter column F. The filter is off at the moment. When on, only the rows where column F is TRUE will be shown.

All the columns that say "hide" are to be hidden after it is all set up

The actual formulas aren't important because they don't fit what you are doing but here they are anyway:

D2 =LEFT(B2,LEN(Search Criteria::$C$2))=Search Criteria::$C$2

E2 =AND(C2≥Search Criteria::$C$3,C2≤Search Criteria::$C$4)

F2 =AND(D2,E2)

fill down with all three

In the Search Criteria Table,

C2 = B2&""

C3 =IF(B3="",0,B3)

C4 =IF(B4="",9999,B4)

This is actually a very simple example. You can get wild with ORs and ANDs and XORs and all kinds of whacky stuff that would be impossible to do with a simple filter.

If you provide more specifics on your table and what you need to do, it would be possible to provide an example that fits your data better.

No, doesn't have to be that complicated. The real point is to have a column in the table with a formula that is TRUE for rows that are to be shown. The second table was so the user does not have to change the formula every time they want to filter on something different, they just type a different criteria into a cell.

Could you explain what you mean by "execute them separately"? An example and a screenshot would help.

You might get some ideas about how to use filters in this video. The idea is to build your logic in a special column and base your filter on that column.

SG

Follow along with the video. It doesn't have to be that complicated.

SG