Wed, 13 Jun 2018

# An update to this blog.

After roughly 2 years I found some time again to touch this blog. I have to move hosting providers and thus I'm migrating everything to Docker containers. While working on that I noticed that half of this blog isn't working, upgraded to Pyblosxom 1.5.3 (deprecated), and hopefully fixed most of the broken stuff. Maybe I even get to write more. In the meantime at least the obvious stuff is fixed and working. Enjoy!

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

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

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

Fri, 27 Feb 2015

A test image

test image

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

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