Calculating imported range of cells

2
votes

I want to import ranges of cells in a row to another tab in Google Sheets, where I divide these rows into columns (68 rows at a time, with to blank rows in between).

I'm importing Sheet2!A2:A69 from Sheet2 to Sheet1!C4:C71 via =ARRAYFORMULA(Sheet2!A2:A69). I then want to import the next 68 rows (with two blanks in between) at Sheet2!A72:A139 to Sheet1!D4:D71. And then continue that up until column Z or so.

How can I change my formula to calculate which cells I want to import without having to manually write them in for each column in Sheet1?

1
google-sheetsgoogle-sheets-formula

1 Answers

2
votes

You could try using the OFFSET function combined with the COLUMN function.

Put this formula in C4 and copy it across as far as needed.

=ARRAYFORMULA(OFFSET(Sheet2!$A$2,69*(COLUMN()-3),,68))