How to work over rows from all other sheets in Google Sheets?

Question

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc.

In the summary sheet I have this formula (to calculate Uber costs):

=FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER"))

How do I make it automatically span over the rest of the sheets instead of just Jan20?

Answer 1

• to span horizontally, paste this formula where you need it and drag to the right:

  =FILTER(    INDIRECT(TEXT(EOMONTH("01/01/2019", COLUMN(A1)-2)+20, "mmmdd")&"!F2:F"), 
   REGEXMATCH(INDIRECT(TEXT(EOMONTH("01/01/2019", COLUMN(A1)-2)+20, "mmmdd")&"!D2:D"),
   "UBER"))

• to span vertically, paste this formula where you need it and drag it down:

  =FILTER(    INDIRECT(TEXT(EOMONTH("01/01/2019", ROW(A1)-2)+20, "mmmdd")&"!F2:F"), 
   REGEXMATCH(INDIRECT(TEXT(EOMONTH("01/01/2019", ROW(A1)-2)+20, "mmmdd")&"!D2:D"),
   "UBER"))

Answer 2

I finally arrived at my own solution using the extremely handy query function in combination with the array range selector:

=query({'Jan20'!A2:F;'Feb20'!A2:F;'Mar20'!A2:F}, "select Col1,Col4,Col6 where Col4 contains 'UBER' and Col1 is not NULL")

I still have to manually add in the new months (there is only 12 of them per document anyway), but it is much simpler than the original formula.