Just how do I link a cell in Google Spreadsheets to a cell in an additional record?

I have a month-to-month spread sheet that relies upon numbers from the previous month. I would certainly such as to import these values dynamically as opposed to reducing and also pasting them. Is this feasible? Just how do I do it?

0
2019-05-05 21:48:49
Source Share
Answers: 4

FYI if you intend to reference an additional sheet within that spread sheet, the sheet name need to NOT be re - priced estimate :

usage

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

as opposed to

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

0
2019-12-04 09:36:54
Source

In the new user interface, you need to merely have the ability to type = in the cell, after that simply most likely to the various other sheet and also select the cell you desire. If you intend to do it by hand, or are making use of the old user interface, you can merely do =Sheet1!A1, where Sheet1 is the name of the sheet, and also A1 is the cell on that particular sheet you respect. This corresponds Microsoft Excel.

0
2019-05-09 05:50:24
Source

IMPORTRANGE() seems the function you desire.

From the Google spreadsheets function list :

Google Spreadsheets allows you reference an additional workbook in the spread sheet that you're presently editing and enhancing by utilizing the ImportRange function. ImportRange allows you draw several cell values from one spread sheet right into an additional. To create your very own ImportRange solutions, enter =importRange (spreadsheet-key,. array ). For languages where comma is. made use of for decimal splitting up, usage a. semicolon as opposed to a comma to. different debates in your formula.

Spreadsheet-key is a STRING which is. the key value from the spread sheet. LINK.

Array is a STRING standing for the. series of cells you intend to import,. additionally consisting of the sheet name. (defaults to first sheet ). You can. additionally make use of an array name if you favor.

Considered that both debates are. STRINGs, you require to confine them in. quotes or describe cells which have. string values in them.

As an example :

= importrange (" abcd123abcd123", "sheet1!A1 :C10" ) "abcd123abcd123" is. the value in the "key=" feature on. the URL of the target spread sheet and also. " sheet1!A1 :C10" is the array which is. wanted to be imported.

= importrange (A1, B1 ) Cell A1 has the string ABCD123ABCD123 and also cell B1. has sheet1!A1 :C10

Note : In order to make use of ImportRange, you. require to have actually been included as a visitor or. partner to the spread sheet from. which ImportRange is drawing the information. Or else, you'll get this mistake : " #REF! mistake : The asked for. spread sheet key, sheet title, or cell. array was not located."

" key" is, certainly, the string in the URL for the spread sheet that matches to the key= parameter.

I simply examined it by developing 2 spread sheets. In cell A1 of the first, I placed a string. In cell A1 of the 2nd, I placed =importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and also it presented the string from the first spread sheet. (Your key will clearly be various. )

(The layout of the function might rely on your place. In France the formula is not legitimate with a comma, so you'll require to change it with a semi-colon : =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1") )

NOTES :

  1. Google presently establishes a tough restriction of 50 "cross-workbook reference solutions" per spread sheet. Resource : Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer )

  2. The "new" Google Spreadsheet (quickly to be the typical ) gets rid of the 50 restriction of 50 "cross-workbook reference solutions (Google Support ) (h/t Jacob Jan Tuinstra )

  3. In the "new" Google Sheets you additionally make use of the whole URL as the key (Google Support ) (h/t Punchlinern )

0
2019-05-07 22:13:37
Source

below is just how I did it (reimplemented 'importrange ( )' ) :

  • open the manuscript editor (" devices" -> "manuscripts" -> "manuscript editor" )
  • give a function similar to this (with no checks, this needs to be boosted yet you get the basic suggestion ) :
function REMOTEDATA(inKey, inRange) {

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

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

  return outData;
}
  • utilize this formula similar to this in your spread sheet :
=SUM(REMOTEDATA("key", "SheetName!A1:A10"))

" key" is the key of the record, you locate it in the "key= xyz" parameter of the record URL.

the documents for 'spreadsheet services' gives even more details on the subject.

0
2019-05-07 22:06:02
Source