Automatically pull an entire sheet of data between google spreadsheets

In November 2011, I wrote a blog post titled ‘How to automatically pull data between different Google Spreadsheets‘ – which was based on a feature called ‘ImportRange’. Although nearly 5 years old, this particular blog post is one of my most frequented and certainly the most commented on post that I have ever written.

I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at https://importsheet.com/ – and this does exactly what it says on the tin. It allows you to easily import (or export) a sheet from one file to another. So for example, I use google sheets to log the work that I do for each of my clients, with each client having a separate workbook (this means that I can share that workbook with them, without them seeing other contracts that I work on). I can then have a master dashboard, which imports copies of these sheets into a single location, and using simple functions such as the = function, I can then pull key data out of each of the sheets into my dashboard. Using Importsheet rather than the ImportRange feature is much easier, quicker and less likely to have problems.

The add on is a commercial tool, that does have a pricing plan, however the free version does what most people will want. Obviously I have no idea how the pricing plan may change in the future – they may choose to get rid, or reduce the functionality of the free version, so I wouldn’t suggest that people invest lots of time creating high stakes activity with this add on (unless they are prepared to pay in the future) – but certainly for the moment, this looks rather neat.

How to display the sheet name in a cell in an Excel spreadsheet

I use Excel a lot, not just for crunching numbers, but for creating teaching resources, lesson planning, managing my accounts and invoices and various other uses. One feature that I often use, is the ability to have the sheet name appearing inside a cell in the spreadsheet – so for example with my invoices – I rename the sheet name with the invoice number, this then updates the invoice within the sheet.

To do this I use the following formula below.

This may seem a complex formula, and it doesn’t matter if you don’t fully understand it (I don’t), you just need to copy and paste this into a cell in the spreadsheet, and the sheet name will appear. If you change the sheet name, the cell will change accordingly.

The only caveat is, that the workbook has to have been saved at some point for this to work – so if you do this with a new workbook, it won’t work until it has been saved.

This technique is unique to Microsoft Excel, it doesn’t work with other spreadsheet tools such as Open office, Google sheets or Apple’s Numbers.