I'm maintaining this soccer stats sheet and have different games on different sheets. To build up sums I like to reference data from the previous sheet. It took me some trial and error to get going. Essentially it requires a custom function and the use of INDIRECT.

For the custom function go to Tools > Script Editor and paste the following code

javascript /** * Retrieves a reference to the previous sheet, or null * input is unused. */ function prevSheet(input) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var sheets = sheet.getSheets(); var prev = null; for(var i = 0; i < sheets.length; i++) { if (sheets[i].getSheetId() == sheet.getSheetId()) { return prev.getName(); } prev = sheets[i]; } return null; };

The code is also available here as an add-on.

To use the function you need to use the INDIRECT function, for example to reference cell A2 on the previous sheet:

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!A2")

To be able to copy the formula around and keeping the references, use

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!" & CHAR(64+COLUMN()) & ROW()

This was very helpful to get me started.

posted at 08:23 | path: /web | permanent link to this entry