Array Formula with Filter for merging multiple sheets in Google Sheets - problem with empty sheet

Question

I am using this formula to merge multiple sheets (about 100, but for simplicity just stick with 3 sheets: Sheet1, Sheet2 and Sheet3), in which I do not know how many non-empty rows I have (also, they increase dinamically).

={FILTER(Sheet1!A2:L;LEN(Sheet1!A2:A));FILTER(Sheet2!A2:L;LEN(Sheet2!A2:A));FILTER(Sheet3!A2:L;LEN(Sheet3!A2:A))}

The formula works well if all the sheets have at least one row filled with values. However, the problems is that if, for example, Sheet2 is empty, the formula gets me an error:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

How can I fix this error?

Answer 1

The problem is: When your sheet is empty, error message is returned. But this message is just one cell. Your array to keep it's shape needs all rows to have 12 columns (A to L) That's why you have "array literal..." message

You should set a condition for that creates an empty row of 12 cells in case of error with empty content.

Try:

Iferror(FILTER(Sheet1!A2:L;LEN(Sheet1!A2:A));{""\""\""\""\""\""\""\""\""\""\""\""}) 

for a sheet that you know as empty

\ separator works for some sheet locales but for USA, UK use comma (as I see you use semicolon between your values in formulas so \ should work).