Google-sheets – Problem with results of filter function from tabs that have no data yet

formulasgoogle sheetsgoogle-sheets-arraysgoogle-sheets-query

I made a master sheet that combines all data from 12 tabs within the workbook sheet. These tabs use query functions and importrange from other sheets. Some of sheets have no data yet like May 2021 sheet.. So when query/importrange this sheet ID , a result of #value appear.

Now the question when combining all of these tabs with the following formula

={filter(January!A2:O,len(January!A2:A));filter(February!A2:O,len(February!A2:A));filter(March!A2:O,len(March!A2:A));filter(April!A2:O,len(April!A2:A));filter(June!A2:O,len(June!A2:A));filter(July!A2:O,len(July!A2:A));filter(August!A2:O,len(August!A2:A));filter(September!A2:O,len(September!A2:A));filter(October!A2:O,len(October!A2:A));filter(November!A2:O,len(November!A2:A));filter(December!A2:O,len(December!A2:A))}enter image description here

But it returns error.. It can filter only tabs that have data while tabs that will have data in future are problem for this formulaenter image description here

This is the sheet that has the reference data for the filter function. It query/importrange data from other sheets.. No January and February has data while March and coming months returns error because the source data still are empty

Best Answer

To avoid the issue with the FILTER function, use a query instead.

=QUERY({January!A2:O;
        February!A2:O;
        March!A2:O}, "where Col1 is not null",0)