How do I link a cell in Google Spreadsheets to a cell in another document?

Question

I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?

Answer 1

importRange() appears to be the function you want.

From http://docs.google.com/support/bin/answer.py?hl=en&answer=98757

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, enter =importRange(spreadsheet-key, 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 URL.

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.

For example:

=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 contains sheet1!A1:C10

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 key= parameter.

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 be replace it with a semi-colon: =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))

Answer 2

FYI if you want to reference another sheet within that other spreadsheet, the sheet name should NOT be re-quoted:

use

=importRange("tgR2P4UTz_KT0Lc270Ijb_A","Sheet Name!A1:A10")

instead of

=importRange("tgR2P4UTz_KT0Lc270Ijb_A","'Sheet Name'!A1:A10")

Answer 3

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!A1, where Sheet1 is the name of the sheet, and A1 is the cell on that sheet you care about. This is identical to Microsoft Excel.

Answer 4

I found this syntax using Insert -> Define new range RANGE FOR REFERENCING OTHER SPREADSHEET:

Whole A column: 'Spreadsheet With Spaces in the name'!A:A

Whole B column: 'Spreadsheet With Spaces in the name'!B:B

etc.

Then you can use it as:

=COUNTIF('First Page'!B:B, "valueToMatch")

Answer 5

here is how i did it (reimplemented 'importrange()'):

• open the script editor ("tools" -> "scripts" -> "script editor")
• provide a function like this (without any checks, this has to be improved but you get the general idea):

function REMOTEDATA(inKey, inRange) {

  var outData;  
  var ss = SpreadsheetApp.openById(inKey);

  if (ss) {
     outData = ss.getRange(inRange).getValues();
  }

  return outData;
}

• use this formula like this in your spreadsheet:

=SUM(REMOTEDATA("key", "SheetName!A1:A10"))

"key" is the key of the document, you find it in the "key=xyz" parameter of the document url.

the documentation for 'spreadsheet services' provides more information on the topic.

Answer 6

I did it very easy way by using Query for example I have 2 sheets ABC and XYZ and I want to import range from A1 to C30 from Abc into XYZ, click the cell where you want the range to be appear and write this:

=QUERY(ABC!A1:C30)

Note: If your sheet name have spaces then of course you write this:

=QUERY('ABC 1'!A1:C30)