Google Sheets - how to show sheet name in SELECT result of QUERY() function

Question

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)

)

z.. · Accepted Answer · 2024-05-09 20:35:31Z

1

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

edited May 9, 2024 at 20:35

answered May 9, 2024 at 15:28

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

coder Over a year ago

It fails for the second condition INDIRECT(A2&"!A3:E"), I tried adding the ROW() like in the first condition but it doesn't work

coder Over a year ago

It doesn't work. It throws this error Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D. Query only takes 3 arguments, but this is argument number 4.

z.. Over a year ago

I replaced D with Col4 and E with Col5.

Collectives™ on Stack Overflow

Google Sheets - how to show sheet name in SELECT result of QUERY() function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related