I am using importrange to pull data from my responses page to my spreadsheet. I have completed the first sheet but I now need to go into the sheet to follow on where sheet one has run out of space. The problem is I have copied the importrange script to all cells in the column, but the range hasn't automatically changed. I want the range to say A2:A2 to change to A3:A3 etc. Can this be done so I don't have to change each one by hand

Here is an example

 =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1cBaiqy_VZRvM90XHW4doW6phqZO02OgoFdhNN1COgck/edit", "Time!e18:e18")

and I want when copied to the next cell to be

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1cBaiqy_VZRvM90XHW4doW6phqZO02OgoFdhNN1COgck/edit", "Time!e19:e19") 

and so on...


2 Answers 2

  • if you want to drag down the formula use:

    =IMPORTRANGE("1cBaiqy_VZRvM90XHW4doW6phqZO02OgoFdhNN1COgck", "Time!E"&ROW(A18))

  • if you want to drag to the right use:

    =IMPORTRANGE("1cBaiqy_VZRvM90XHW4doW6phqZO02OgoFdhNN1COgck", "Time!E"&COLUMN(R1))

  • Ok thanks I'll try this out
    – Peteh
    Jun 20, 2019 at 6:44
  • 1
    That worked great, thanks. Will save a lot of time now 🙂
    – Peteh
    Jun 20, 2019 at 6:58
  • One more problem. I am using the following code in many cells of the sheet, =SPLIT(REGEXREPLACE("" & S2, "(\d)", "$1,"), ",") not all are going to contain data at any one time. How do I stop the empty cell value notice from appearing.
    – Peteh
    Jun 21, 2019 at 6:48

Instead of A2:A2 use A2 as it's shorter and also will simplify the IMPORTRANGE formula.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1cBaiqy_VZRvM90XHW4doW6phqZO02OgoFdhNN1COgck/edit", "Time!e" + row())

Instead for row() your could use column() or any variant like row() + 5 to get the required row number of the cell to import.

  • Thanks for your help, I will give that a go later 🙂
    – Peteh
    Jun 18, 2019 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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