Friday, June 24, 2011

yogi_Consolidate A Dynamic Range Of Identical Sheets Into One Master

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

jalabiso said:
I need to have the SUM of the same CELL position from a range of worksheets with exactly the same layout.
In the cell C3 of the master sheet i would write:
=SUM(SomeSheetName1!C3;SomeSheetName2!C3;SomeSheetName3!C3;..and so on) All sheets (including master) will look the same. Just Master has the SUM of all sheets.
The problem is that the number of sheets to consolidate is variable, and not controlled by me. Therefore it would be too time consuming to keep the SUM line updated (always more than 40 sheets in that SUM)
I have no idea how to do it, but let's say I would keep the master in a different file, i could then reference all child sheets like this (with fantasy formula): =SUM(AllSheetsFrom(ChildDoc!C3:C3)
now, AllSheetsFrom is just fruit of my fantasy.
Is there anything real that could work that way?
Google Docs spreadsheet does not support the so called 3-dimensional ranges.
I had a little play with a brute force low-tech solution that would do the job even when sheets are added/deleted/modified as presented in the following: