I have this query
=ARRAYFORMULA(QUERY({
IFERROR({INDIRECT(A1&"!C2:E"), IF(INDIRECT(A1&"!C2:C")<>"", A1, "")}, {"","","",""});
IFERROR({INDIRECT(A2&"!C2:E"), IF(INDIRECT(A2&"!C2:C")<>"", A2, "")}, {"","","",""});
IFERROR({INDIRECT(A3&"!C2:E"), IF(INDIRECT(A3&"!C2:C")<>"", A3, "")}, {"","","",""});
IFERROR({INDIRECT(A4&"!C2:E"), IF(INDIRECT(A4&"!C2:C")<>"", A4, "")}, {"","","",""});
IFERROR({INDIRECT(A5&"!C2:E"), IF(INDIRECT(A5&"!C2:C")<>"", A5, "")}, {"","","",""});
IFERROR({INDIRECT(A6&"!C2:E"), IF(INDIRECT(A6&"!C2:C")<>"", A6, "")}, {"","","",""});
IFERROR({INDIRECT(A7&"!C2:E"), IF(INDIRECT(A7&"!C2:C")<>"", A7, "")}, {"","","",""});
IFERROR({INDIRECT(A8&"!C2:E"), IF(INDIRECT(A8&"!C2:C")<>"", A8, "")}, {"","","",""});
IFERROR({INDIRECT(A9&"!C2:E"), IF(INDIRECT(A9&"!C2:C")<>"", A9, "")}, {"","","",""});
IFERROR({INDIRECT(A10&"!C2:E"), IF(INDIRECT(A10&"!C2:C")<>"", A10, "")}, {"","","",""});
IFERROR({INDIRECT(A11&"!C2:E"), IF(INDIRECT(A11&"!C2:C")<>"", A11, "")}, {"","","",""});
IFERROR({INDIRECT(A12&"!C2:E"), IF(INDIRECT(A12&"!C2:C")<>"", A12, "")}, {"","","",""})
}, "select Col4, Col1, Col2 where Col3 contains 'AutoService'", 0))
which returns the following error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
This is because I don’t have yet created last 3 sheets
Basically I want to select 3 columns from 12 various sheets with name of the month. Also I want to include name of the month rows are from.
2
Here’s one alternative approach which you may adapt accordingly:
=let(Σ,tocol(,1), reduce(Σ,tocol(A1:A12,1),lambda(a,c,vstack(if(iserr(+a),Σ,a),let(Λ,filter(iferror(indirect(c&"!C2:D"),iferror(now()/0)),regexmatch(indirect(c&"!E2:E"),"(?i)autoservice")),if(iserror(Λ),Σ,hstack(wrapcols(c,rows(Λ),c),Λ)))))))
IMPORTANT: When a new tab (say Apr) is created, indirect() in this scenario does not automatically pick up this new tab data for consideration. To prompt for consideration instantly, make some kind of edit/change anywhere in the sheet; it could be as simple as ticking a checkbox or entering 1 in a cell for example…
1