Google Sheets Or statement while copying data from other tabs

Question

I have a Google sheet containing multiple tabs. The first four tabs are libraries of programs in our library sorted type (i.e. standalone programs, series, special events, and local programs). I want to parse this data for my most common use in more tabs based on the topic of the program by filtering for any, say, documentaries in these first four sheets and copying the row to a 'documentaries' tab.

I have code that works to copy information from the 'Programs' sheet to the new sheet. I want to augment this formula to search across the next 3 sheets as well (Series, Pledge Programs, Local Productions). I've tried nesting an 'or' statement within this which doesn't work. I've googled what I'm trying to do without finding examples that are quite right.

Works:

=filter(Programs!A:O,Programs!$E:$E="Documentary")

Doesn't work:

=filter(Programs!A:O,Programs!$E:$E="Documentary") or (Series!A:O,Series!$F:$F="Documentary")

Ultimately I would like every documentary from the first four sheets to populate into a Documentary sheet.

I've also been searching out different ways to use the Filter function without any luck. I suspect I can't directly run filter on two different sheets but that there may be a way to combine results from two different filter queries? I saw that alluded to, but no examples of how to do it. — Tod Poirier

player0 player0 · Accepted Answer · 2019-03-29T06:08:34

all you need is this:

=QUERY(
 {FILTER(           Programs!A:O,            Programs!E:E = "Documentary");
  FILTER(             Series!A:O,              Series!F:F = "Documentary");
  FILTER(  'Pledge Programs'!A:O,   'Pledge Programs'!E:E = "Documentary");
  FILTER('Local Productions'!A:O, 'Local Productions'!E:E = "Documentary")},
 "where Col1 is not null and not Col1 = '#N/A'")

Google Sheets Or statement while copying data from other tabs

1 Answers