Is there a dynamic way of accessing ranges from multiple tabs?

Question

My Google Sheets document contains 4 tabs: June2019, July2019, August2019, and Master. In the Master tab, I collect the data from all monthly tabs, which all have the same layout. In the Master tab, I have functions such as:

=SUM(June2019!Profits, July2019!Profits, August2019!Profits)

or

=COUNTA(QUERY({June2019!A3:C;July2019!A3:C;August2019!A3:C},"select Col2 where Col2 = 'L' and Col3 = 'P'"))

As the year progresses and I add more monthly tabs to my Google Sheets document, I don't want to have to go through all the formulas on my Master tab and update their arguments to include the new monthly tabs, for example to:

=SUM(June2019!Profits, July2019!Profits, August2019!Profits, September2019!Profits)

On the Master tab, is there a way to specify 1 time, the list of monthly tabs you want to pull data from? And then have the formulas use as range arguments, the ranges from monthly tabs in that list? This way I wouldn't have to update the monthly-tab-ranges for every formula's range argument every time a new month is added. I would just add a month to the list. The approach would probably be different for the SUM() formula and the QUERY() formula.

I cannot think of a solution, as the range arguments aren't strings. If they were, you could possibly list all the tab names in column A, then append something like "!A3:C" to each argument in column A for column B, and use the entire column B as an argument for the range parameter, something like:

=COUNTA(QUERY(B1:B5,"select Col2 where Col2 = 'L' and Col3 = 'P'"))

Why not create all the monthly sheets in advance, even though some may not be populated with data? Then edit your formula to include all the sheets. — Tedinoz, Aug 18 '19 at 23:52
@Tedinoz Yes that's always a possibility. In reality there are a lot more monthly tabs and I plan on using the sheet for a long time. So I was hoping for a more elegant method. This way you might also be able to compare different groups of months. — James Bale, Aug 19 '19 at 00:08

score 1 · Answer 1 · answered Aug 19 '19 at 08:51

one way would be to account it in advance with IFERROR

=SUM(IFERROR(June2019!Profits), 
     IFERROR(July2019!Profits), 
     IFERROR(August2019!Profits), 
     IFERROR(September2019!Profits))

this way as soon as the September is created it will be accounted (eg if such sheet does not exist it will be left out from calculation)

Is there a dynamic way of accessing ranges from multiple tabs?

1 Answers1

Linked