Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
This Q&A I gave, might be of interest as well: webapps.stackexchange.com/a/42960/29140 –  Jacob Jan Tuinstra Apr 26 '13 at 6:22

1 Answer 1

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

share|improve this answer
    
This is exactly what I'm looking for, but each time the script runs it adds onto the value that is currently in the cell, so I'm getting more than the sum of the sheets in the cell each time it runs. How can I make it delete/replace the current value and just add the sum of the other sheets? –  Keith Sep 23 at 4:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.