Can I automatically run another Goolge Sheets FILTER after/below a FILTER?-CodePudding

I am trying to make a simple grocery shopping list, where items can be checked and added to a filtered list. There will be different categories in different tabs/sheets (Produce, Dairy, Snacks, Household and Other). I currently have this filter in cell A1 of the SHOPPING LIST sheet:

=FILTER(Produce!B2:E,Produce!A2:A=TRUE)

Is it possible to have another filter run automatically after this, once the above filter runs and the number of rows is known? So that all filters run vertically to create the full shopping list:

=FILTER(Diary!B2:E,Dairy!A2:A=TRUE)

And then the next, etc.

=FILTER(Snacks!B2:E,Snacks!A2:A=TRUE)
=FILTER(Household!B2:E,Household!A2:A=TRUE)
=FILTER(Other!B2:E,Other!A2:A=TRUE)

CodePudding user response：

You can combine these formulas with an array:

={
FILTER(Produce!B2:E,Produce!A2:A=TRUE);
FILTER(Diary!B2:E,Dairy!A2:A=TRUE);
FILTER(Snacks!B2:E,Snacks!A2:A=TRUE);
FILTER(Household!B2:E,Household!A2:A=TRUE);
FILTER(Other!B2:E,Other!A2:A=TRUE)
}

The semicolon ; at the end of each formula shows that you want the next one to be placed in the next row. Using a comma , instead would show that you want the next item to be placed in the next column.

The above formula should be able to be copied directly into your sheet. Please let me know if you have any issues with this

CodePudding user response：

You could probably use something like:

={FILTER(Diary!B2:E,Dairy!A2:A=TRUE);
  FILTER(Snacks!B2:E,Snacks!A2:A=TRUE);
  FILTER(Household!B2:E,Household!A2:A=TRUE);
  FILTER(Other!B2:E,Other!A2:A=TRUE)}

CodePudding user response：

or try:

=FILTER({Produce!B2:E; Diary!B2:E; Snacks!B2:E; Household!B2:E; Other!B2:E}; 
        {Produce!A2:A; Diary!A2:A; Snacks!A2:A; Household!A2:A; Other!A2:A}=TRUE)