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

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

Solutons:


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

NOTES:

  1. 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)

  2. 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)

  3. 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:

use

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

instead of

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

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.

Related Solutions

Pin-board effect with CSS [closed]

You can use JavaScript to accomplish this but it can't be done with CSS floats alone. A library like jQuery masonry will do it well. The reason? The specs on floats. In particular #5 which says, "The outer top of a floating box may not be higher than the outer...

Calculate the sum with minimum usage of numbers

Here's a hint: 23 : 11 + 11+ 1 ( 3 magic numbers) 120: 110+ 10 (2 magic numbers) The highest digit in the target number is the answer, since you need exactly k magic numbers (all having 1 in the relevant position) in order for the sum to contain the digit k. So...

Why not drop the “auto” keyword? [duplicate]

Your proposal would be rejected on the basis of backward compatibility alone. But let's say for the sake of argument that the standards committee like your idea. You don't take into account the numerous ways you can initialize a variable widget w; // (a) widget...

Recursive to iterative using a systematic method [closed]

So, to restate the question. We have a function f, in our case fac. def fac(n): if n==0: return 1 else: return n*fac(n-1) It is implemented recursively. We want to implement a function facOpt that does the same thing but iteratively. fac is written almost in...

How can I match values in one file to ranges from another?

if the data file sizes are not huge, there is a simpler way $ join input1 input2 | awk '$5<$4 && $3<$5 {print $2, $5-$3+1}' B100002 32 B100043 15 B123465 3 This Perl code seems to solve your problem It is a common idiom: to load the entire...

Javascript difference between “=” and “===” [duplicate]

You need to use == or === for equality checking. = is the assignment operator. You can read about assignment operators here on MDN. As a quick reference as you are learning JS: = assignment operator == equal to === equal value and equal type != not equal !==...

Compiler complains about misplaced else [closed]

Your compiler complains about an misplaced else because, well, there is an else without a preceding if: // ... for (j=1; j<n-i; j++) { if(a[j]<=a[j+1]) { // ... } // END OF IF } // END OF FOR else { continue; } // ... The else in your code does not follow...

Bootstrap – custom alerts with progress bar

/* !important are just used to overide the bootstrap css in the snippet */ .alertContainer { border-radius: 0 !important; border-width: 0 !important; padding: 0 !important; height: auto !important; position: absolute !important; bottom: 15px !important; left:...

How to Garbage Collect an external Javascript load?

Yes, s.onload = null is useful and will garbage collect! As of 2019, it is not possible to explicitly or programmatically trigger garbage collection in JavaScript. That means it collects when it wants. Although there is cases where setting to null may do a GC...

Math programming with python

At first, what you are looking for is the modulo operator and the function math.floor() Modulo from wikipedia: In computing, the modulo operation finds the remainder after division of one number by another (sometimes called modulus). for example: 12%12=0...

Android slide over letters to create a word [closed]

Here some advice you can use: First for each cell you can create an object that represents the state of that cell: class Cell { char mChar; int row,column; boolean isSelected; } then you can create a 2D array of your cells Cell[][] mTable = ... For views you...

Sum two integers in Java

You reused the x and y variable names (hence the variable x is already defined in method main error), and forgot to assign the ints read from the Scanner to the x and y variables. Besides, there's no need to create two Scanner objects. public static void...

Extend three classes that implements an interface in Java

Using this simplified implementation of the library, using method() instead of M(): interface IFC { void method(); } class A implements IFC { public void method() { System.out.println("method in A"); }; } As akuzminykh mentions in their comment You'd write a...