In this formula I need to show the Sheetname in the first column, either concatenated to Col1 or it could be the first column, like this.
SELECT SheetName,Col1,Col2,Col3,Col4,Col5
I am not even sure what to try. Just to test I tried hardcoding with CONCATENATE() like SELECT CONCATENATE("Jan",Col1)
, it throws error. Tried SELECT "Jan" & Col1
, doesn't work.
I tried this too, it doesn't throw error but it doesn't show the word "Jan"
QUERY({ArrayFormula("Jan "&Jan!A3:E);Feb!A3:E
Formula:
=SORT(
ARRAYFORMULA(IF(A2="[All]",
QUERY({Jan!A3:E;Feb!A3:E;Mar!A3:E;Apr!A3:E;May!A3:E;Jun!A3:E;Jul!A3:E;Aug!A3:E;Sep!A3:E;Oct!A3:E;Nov!A3:E;Dec!A3:E},
"SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 "
& IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')")
& IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
QUERY(INDIRECT("'"&A2&"'!A3:E"),
"SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 "
& IF(B2="[All]",""," AND LOWER(D)=LOWER('" & B2 & "')")
& IF(C2="[All]",""," AND LOWER(E)=LOWER('" & C2 & "')")
)
)
),IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)
)
Try this out:
=SORT(IF(A2="[All]",
QUERY({Jan!A3:E,IF(ROW(Jan!A3:E),"Jan");
Feb!A3:E,IF(ROW(Feb!A3:E),"Feb");
Mar!A3:E,IF(ROW(Mar!A3:E),"Mar");
Apr!A3:E,IF(ROW(Apr!A3:E),"Apr");
May!A3:E,IF(ROW(May!A3:E),"May");
Jun!A3:E,IF(ROW(Jun!A3:E),"Jun");
Jul!A3:E,IF(ROW(Jul!A3:E),"Jul");
Aug!A3:E,IF(ROW(Aug!A3:E),"Aug");
Sep!A3:E,IF(ROW(Sep!A3:E),"Sep");
Oct!A3:E,IF(ROW(Oct!A3:E),"Oct");
Nov!A3:E,IF(ROW(Nov!A3:E),"Nov");
Dec!A3:E,IF(ROW(Dec!A3:E),"Dec")},
"SELECT Col6,Col1,Col2,Col3,Col4,Col5 WHERE 1=1 "
& IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')")
& IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
QUERY({INDIRECT(A2&"!A3:E"),IF(ROW(INDIRECT(A2&"!A3:E")),A2)},
"SELECT Col6,Col1,Col2,Col3,Col4,Col5 WHERE 1=1 "
& IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')")
& IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")
)
)
,IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)
)