How to append rows from multiple sheets into one "master sheet"?

Question

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.

Answer 1

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.

Answer 2

Formula

=FILTER({Sheet1!A:D; Sheet2!A:D},LEN({Sheet1!A:A; Sheet2!A:A}))

Explanation

Doing operations with open ended references usually introduces a lot of blank rows or columns. If you are not aware of this, this could lead to confusions, as it look to happened to the OP.

Besides the use of the array notation, consider to filter the resulting array to avoid having several blank rows/columns between the appended data. There are several ways to do this, one of them is by using FILTER as was shown on the first section.