1

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

1

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)
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.