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
add comment

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
add comment

Your Answer

 
discard

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