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

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?

• can you share a copy of your sheet? Mar 17, 2019 at 15:23

• 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"))``````

• Is this the correct answer? The question is about spanning across sheets (in the formula variables), not across rows or columns. Mar 22, 2019 at 11:37
• from your question was understood that you are located at "summary" sheet and in that sheet you need to create a summary of each of the 12 sheets and span it over "summary" sheet in such way that you wouldn't need to copy-paste sheets' references/names eg. edit 12 different formulas 12 times. Mar 22, 2019 at 14:20

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.