Sun, 18 Dec 2016

Reference the Previous Sheet in Google Spreadsheets

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

/**
 * 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 03:23 | path: /web | permanent link to this entry

Fri, 27 Feb 2015

A test image

test image

posted at 00:38 | path: / | permanent link to this entry

Tue, 24 Feb 2015

A new blog entry in Markdown format

I took the markdown plugin from here and fixed a few issues, most notably making it an entry parser, losely based on the rst.py plugin.

This is awesome, because now I can use github to actually write blog entries in the browser - maybe, just maybe making the hurdle a bit less to write a few posts.

The resulting code can be found in my new github repository.

And yes, I know, version 1.5 will bring this by default - but I am on Debian Wheezy which runs 1.4.

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

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.