Google-sheets – In Google Spreadshets, how can you loop through all available sheets not knowing the total count

google sheetsgoogle-apps-script

I'm keeping stats for my daughter's baseball team. I have a sheet for each game, each one identical in structure. I want to create a summary sheet that totals numbers from the other sheets. How should I construct the formulas to reference N number of sheets?

Example:

=G1!C2+G2!C2+G3!C2

I don't want to go back and change each formula when a new sheet is added.

Update: Searching some more at Google Help, I don't think this is supported. What I'm doing is referred to as a 3D range and though it is supported in Excel, it is not supported in Google Spreadsheet.

Best Answer

If you don't mind using a macro, you can do the following. Add this script to your spreadsheet (Tools -> Scripts -> Script editor):

function getTotalSum(cell) {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var sum = 0;
    for (var i = 0; i < sheets.length ; i++ ) {
        var sheet = sheets[i];
        var val = sheet.getRange(cell).getValue();

        if (typeof(val) == 'number') {
            sum += val;   
        }       
    }

    return sum;
}

and use the formula =getTotalSum("A1"), where "A1" is the cell which you want to sum up in all the sheets (except the one you putting the formula in).

I made a test script which you can take a look at: https://spreadsheets.google.com/ccc?key=0Ao3E95ou1MgbdG45blRvWlVaYkxFY01PQUhvZkZQcEE&hl=en&authkey=CPyZqxk

Related Topic