Concatenation of multiple ranges not working in Google Sheets

Question

I have two sheets in the same workbook, "Sheet1" and "Sheet2"

Both sheets have the same column headings and only have data within A:G

In a third "combined" sheet named "CombinedSheets" I am trying to bring in all of Sheet1 and Sheet2 and display the data.

I am trying to use this:

={Sheet1!A:G; Sheet2!A2:G}

However, this is only bringing in the data from the first sheet in the array (Sheet1). I've tried switching the array around but still, it only loads data from the first sheet in the array.

Both Sheet1 and Sheet2 have their data loaded via ARRAYFORMULA() (if that makes a difference).

Any thoughts on how I can troubleshoot this, or is there another way to combine ranges into a sheet?

Answer 1

You probably just need to skip empty rows. Try something like this:

=query({Sheet1!A:G;Sheet2!A:G},"Select * where Col1 is not null ")

Answer 2

it's there, you just need to scroll down on row 1000 to see the second sheet. it's better if you do it like this:

={FILTER(Sheet1!A:G, Sheet1!A:A<>""); FILTER(Sheet2!A2:G, Sheet2!A2:A<>"")}

or:

=QUERY({Sheet1!A:G;Sheet2!A2:G}, "where Col1 is not null")