Linking Google Sheet Cells Between Sheets (aka Spreadsheets)

This was something I’ve only done occasionally in Excel since I’ve started this blog, and never had the need to in Google Sheets – Excel has the capability to link cells from one workbook to cells in another workbook. This is also referred to as an external reference.

This usually is done in the format:

=[Budget.xlsx]Annual!C10

Where :
Budget.xlsx  is the name of the spreadsheet file
Annual  is the name of the worksheet (or tab)
C10 is the cell that you wish to import

Generally I’ve used a VBA function to import ranges of cells from one spreadsheet to another.

Google has something similar for Google Sheets, however it enables you to insert a formula in one cell and import a range that is updated from the original sheet. However, as it is a web reference then you need to refer to the website address.

Google Sheets have website addresses something like the following:

https://docs.google.com/spreadsheets/d/
1gyVRjhQzf6z63pH3rOvxvZrBTghaHPH1HNOGXvLIHlA/edit#gid=0

The area highlighted in green above is called the spreadsheet key. You will need to make note of this key for inserting in the Google Sheet formula.

The formula for importing values from one Google Sheet to another is as follows:

=importRange(“key”, “SheetName!Cell”) – for single cell import

=importRange(“key”, “SheetName!Cell:Cell”) – for a cell range import

So using the key from the example above, assuming the Sheet name is ‘Products’ the formula would look something like the following:

=importRange(“1gyVRjhQzf6z63pH3rOvxvZrBTghaHPH1HNOGXvLIHlA”, “Products!A3”)
– for single cell import

=importRange(“1gyVRjhQzf6z63pH3rOvxvZrBTghaHPH1HNOGXvLIHlA”, “Products!A3:A58”)
– for a column cell range import

=importRange(“1gyVRjhQzf6z63pH3rOvxvZrBTghaHPH1HNOGXvLIHlA”, “Products!A3:F58”)
– for a multi-column cell range import

The beauty of this is that you can insert this formula at the top of a range and import an entire range of values without having to reference each one individually, and they are updated from one spreadsheet to another. Nice. :-)

No related content found.

Additional Resources

Speak Your Mind

*