Google-sheets – How to select a cell from all spreadsheets

google sheets

I am trying to find a function that selects from a certain cell from all spreadsheets. For example, I want to calculate AVERAGE('Sheet1'!B3, 'Sheet2'!B3, 'Sheet3'!B3), but I don't want to explicitly write SheetX, I am rather looking for something like AVERAGE('allsheets':B3)

The reason I am doing this is that I would like the average to be recalculated whenever I add a new sheet.

I am using Google Spreadsheets, I am sure I can write a script to do that, but I think such a trivial problem might have a solution already.

Best Answer

Assuming you meant 'selecting from a certain cell from all sheets' (in one spreadsheet), custom function written in GAS that does that could look like this:

function avgCellAllSheets(cell) {
var arr = [];
SpreadsheetApp.getActive()
    .getSheets()
    .forEach(function (s) {
        arr.push(s.getRange(cell)
            .getValue());
    });
return arr.reduce(function (cur, prev, i) {
    return cur + (prev - cur) / (i + 1);
},0);
}

Fomula to be entered in the sheet is 

=avgCellAllSheets(cell)

example:

=avgCellAllSheets("A1")

However, as custom functions like that seem to suffer from memoization, it will probably not auto-update after adding a sheet. There are workarounds, e.g. adding a dummy parameter, let's say the content of a certain cell that when incremented 'forces' a recalculation of the formula)

Related Topic