Google-sheets – How to append rows from multiple sheets into one “master sheet”

google sheets

I've created several small sheets, all with the same number of columns, and I'd like to produce one large sheet consisting of all the rows from the smaller sheets. I don't want to perform any deduplication, since each row contains important data.

Is there any way I can do this? The closest thing I've been able to find is: 

={'Sheet1'!A:D, 'Sheet2'!A:D}

The trouble here is that the columns are appended to one another, not the rows. Meanwhile, if I do this (note the semicolon instead of the comma):

={'Sheet1'!A:D; 'Sheet2'!A:D}

I get the data from the first sheet and no data from the second.

Best Answer

Give row numbers instead of column letters, and separate the ranges by semicolon ; (vertical stacking instead of horizontal).

={Sheet1!1:3; Sheet2!2:7}

This assumes both sheets 1 and 2 have the same number of columns. If this is not the case, you'll need to specify which columns you want:

={Sheet1!A1:Z3; Sheet2!A2:Z7}

To get all rows of another sheet, use a construction with indirect:

=arrayformula({Sheet1!1:4; indirect("Sheet2!1:" & max(row(Sheet2!A:A)))})

Here, "Sheet2!1:" & max(row(Sheet2!A:A)) constructs a string such as "Sheet2!1:1000", with the latter number obtained as the maximum of row numbers.

Related Topic