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

  • 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 '19 at 6:44
  • 1
    That worked great, thanks. Will save a lot of time now 🙂 – Peteh Jun 20 '19 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 '19 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 '19 at 6:41

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.