0

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?

|improve this question
  • can you share a copy of your sheet? – user0 Mar 17 '19 at 15:23
0

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

|improve this answer
  • Is this the correct answer? The question is about spanning across sheets (in the formula variables), not across rows or columns. – Srid says Reinstate Monica Mar 22 '19 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. – user0 Mar 22 '19 at 14:20
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.