2

How do I copy a formula in Google Sheets that has the address of another sheet in the formula?

Now I have to edit the formula for every cell because when I copy it, it does not stay relative. And when I state "another sheet" I mean doc with a different name (address) not just another page within the same sheet.

2
  • 2
    Please give an example of such a formula. – user79865 Dec 17 '15 at 22:10
  • Here is an example: =IMPORTRANGE("1k4o8odd236MLxIbfM_jI71VwiDl1p--aYU1Sue1Wbz4","Dashboard!s8") except I have taken out several letters so as to protect my sheet. So now when I copy this into different document it will remain at S8 regardless of copying it down to 9, 10, 11 and so on. – user110382 Dec 18 '15 at 14:01
2

In principle, you could do things like

=IMPORTRANGE("1k4o8odd...","Dashboard!S"&row()) 

forming the range string for import based on the row of the current cell.

But I do not actually recommend this. You should not use importrange as an individual cell reference; it's a serious drag on performance. As the name suggests, its purpose is to import the range of all cells you are going to need (maybe the whole sheet you are referring to); put the imported range somewhere (on a separate sheet, perhaps) and work with that data normally. For example, use

=IMPORTRANGE("1k4o8odd...","Dashboard!S1:S")

once and then refer to those imported cells.

0

IF You want to point to only that one cell You need to make your cell references inside the formula absolute with a "$" sign in front :

='Sheet1'!$A$1

if you want it dynamic, remove the $ signs:

=importrange("your doc url","'Dashboards'!$S"&Row())

Also you use importrange to refer to a different doc.

 =importrange("your doc url","'Dashboard'!$S$8")
7
  • Still not answering the question. – user79865 Dec 18 '15 at 14:21
  • @NormalHuman he specifically says he wants s8 to stay static from what I understand, so I believe my edit DID answer the question – Aurielle Perlmann Dec 18 '15 at 14:23
  • It's the opposite; the OP wants a relative reference. "it does not stay relative", says the question. "it will remain at S8 regardless of copying it down to 9, 10, 11" says the comment. – user79865 Dec 18 '15 at 14:26
  • @NormalHuman sorry, i was interpreting his second comment as what he "wanted" to happen – Aurielle Perlmann Dec 18 '15 at 14:29
  • Yes you are right Normal Human, by saying still not answering the question. I want it to be able to copy relative to the cell. I will give you the scenario: I use these for dealer sheets and so what I put on the master dashboard shows up in all the different dealer doc's (separate doc url) and so when I copy it just copies S8 into every cell and then I have to go in and edit each one to s9, s10, s11 and I think that there has got to be a way of copying it like you do in a regular sheet. – user110382 Dec 18 '15 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.