CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
James Bale
James Bale

Reputation: 25

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

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

Upvotes: 0

Views: 73

Answers (1)

player0
player0

Reputation: 1

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)

Upvotes: 1

Related Questions