Just wanted to blog about two really neat things I’ve this afternoon.
Tabcloud
Ever wish you could save every tab you’re looking at and send it to another computer? If so, this plugin is for you.
This is a plug-in for Chrome (and Firefox and iOS apparently) which lets you save an entire browsing session to the cloud and load the same session at a later time on a different computer…it’s wonderful, and it’s perfect for anyone who is doing research and jumping a lot from PC to PC.
Link is here.
Fixing slow spreadsheets with the Excel “Offset” function
I have developed a multi-sheet timekeeping spreadsheet in Excel 2003, and was tasked with implementing it on the boss’ computer (an iMac). The spreadsheet loaded and displayed fine in Excel 2004 for Mac, however it was inexplicably slow as molasses when it came to recalculating cells and switching tabs.
I thought it had something to do with a massive defined range, and I was right.
Luckily, the offset function can be used to dynamically define a range and hence restrict it to only those cells which have data.
This function returns a reference to a set of cells. The definition is as follows:
Offset( range, rows, columns, height, width )
This function is really useful when you want to create dynamically expanding (or contracting) named ranges. An example of its use is:-
=OFFSET(somesheet!$A$2,0,0,COUNTA(somesheet!$D$2:$D$2000),6)
In the example above I am returning a [n] x 6 block of cells, and using a count of up to the first 1999 text entries in column “D” to determine what [n] should be. Count(…) would be used for a column of numbers.
Implementing this dramatically sped up the operation of a large spreadsheet on Excel 2004 for Mac version of Excel.
One word of warning though – if you are running lookups on the named range, and you have set range_lookup to be false, make sure you sort the data within the named range otherwise new entries (with lower key values) will not be returned.
Here’s a link to the original Microsoft Knowledgebase article about it.
