Google Docs: referencing cells in other spreadsheets

I’ve tried to figure this one out a few times and, for some reason, only now FINALLY found the answer.

If you’re like me, you have a number of documents created in Google Drive, many of them spreadsheets. Rather than simply copying a value from one spreadsheet to another, I want to be able to dynamically link them so that they stay updated. This is particularly useful for ever-fluctuating budgets.

The answer comes to us from the importrange function. Start by determining the unique key for the source document by looking at its URL – the key is the long, hexadecimal-like value). Then note the sheet name where the value is stored and then the cell itself.

In your destination spreadsheet, enter the formula as:

=importrange("<spreadsheetKey>", "<sheetName>!<cellRange>")

Now, here’s the trick: when you first do this, you may get a REF error – pay attention to the little corner triangle – the issue isn’t that you made a syntax error but, rather, that you need to grant permission to refer to the external sheet!