# How to select a cell from all spreadsheets?

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.

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 = [];
.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)

• Very nicely prepared. Is it OK nowadays to use the `forEach` method? Feb 21 '15 at 20:07
• @JacobJanTuinstra. Can't see why not ? I guess it all boils down to the task at hand. Both have their advantages and disadvantages, as you probably know... ;-). Thanks for the feedback !
– JPV
Feb 21 '15 at 22:23