IMPORTRANGE() appears to be the function you want.
From the Google spreadsheets function list:
Google Spreadsheets lets you reference
another workbook in the spreadsheet
that you’re currently editing by using
the ImportRange function. ImportRange
lets you pull one or more cell values
from one spreadsheet into another. To
create your own ImportRange formulas,
range). For languages where comma is
used for decimal separation, use a
semicolon instead of a comma to
separate arguments in your formula.
Spreadsheet-key is a STRING which is
the key value from the spreadsheet
Range is a STRING representing the
range of cells you want to import,
optionally including the sheet name
(defaults to first sheet). You can
also use a range name if you prefer.
Given that the two arguments are
STRINGs, you need to enclose them in
quotes or refer to cells which have
string values in them.
=importrange(“abcd123abcd123”, “sheet1!A1:C10”) “abcd123abcd123” is
the value in the “key=” attribute on
the URL of the target spreadsheet and
“sheet1!A1:C10” is the range which is
desired to be imported.
=importrange(A1,B1) Cell A1 contains the string ABCD123ABCD123 and cell B1
Note: In order to use ImportRange, you
need to have been added as a viewer or
collaborator to the spreadsheet from
which ImportRange is pulling the data.
Otherwise, you’ll get this error:
“#REF! error: The requested
spreadsheet key, sheet title, or cell
range was not found.”
“key” is, of course, the string in the URL for the spreadsheet that matches to the
I just tested it by creating two spreadsheets. In cell A1 of the first, I put a string. In cell A1 of the second, I put
=importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and it displayed the string from the first spreadsheet. (Your key will obviously be different.)
(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you’ll need to replace it with a semi-colon:
Google currently sets a hard limit of 50 “cross-workbook reference formulas” per spreadsheet. Source: Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer)
The “new” Google Spreadsheet (soon to be the standard) removes the 50 limit of 50 “cross-workbook reference formulas (Google Support) (h/t Jacob Jan Tuinstra)
In the “new” Google Sheets you also use the entire URL as the key (Google Support) (h/t Punchlinern)
FYI if you want to reference another sheet within that other spreadsheet, the sheet name should NOT be re-quoted:
In the new interface, you should simply be able to type
= in the cell, then just go to the other sheet and pick the cell you want. If you want to do it manually, or are using the old interface, you can simply do
Sheet1 is the name of the sheet, and
A1 is the cell on that sheet you care about. This is identical to Microsoft Excel.