1

I have customer sheets which i must summarize into a single tab so i use this query;

=QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'ROC010'!B2:AB;'ROC157'!B2:AB;'ROC024'!B2:AB;'ROC064'!B2:AB;'ROC073'!B2:AB;'ROC160'!B2:AB;'ROC159'!B2:AB;'ROC084'!B2:AB;'ROC091'!B2:AB;'ROC035'!B2:AB;'ROC161'!B2:AB;'ROC042'!B2:AB;'ROC164'!B2:AB;'ROC049'!B2:AB;'ROC162'!B2:AB;'ROC004'!B2:AB;'ROC077'!B2:AB;'ROC165'!B2:AB;'ROC154'!B2:AB;TRV_REV!B2:AB;'ROC131-3'!B2:AB},"SELECT * where Col9 is not null",0)

i must create new tab for new customer and must udpate the formula, so i use get sheets name using another script called sheetnames() so i can know what new tab that i have added.

and i use

="{"&TEXTJOIN(";",TRUE,B7:B)&"}" to get this:

{'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'ROC010'!B2:AB;'ROC157'!B2:AB;'ROC024'!B2:AB;'ROC064'!B2:AB;'ROC073'!B2:AB;'ROC160'!B2:AB;'ROC159'!B2:AB;'ROC084'!B2:AB;'ROC091'!B2:AB;'ROC035'!B2:AB;'ROC161'!B2:AB;'ROC042'!B2:AB;'ROC164'!B2:AB;'ROC049'!B2:AB;'ROC162'!B2:AB;'ROC004'!B2:AB;'ROC077'!B2:AB;'ROC165'!B2:AB;'ROC154'!B2:AB;'TRV_REV'!B2:AB;'ROC131-3'!B2:AB}

How to replace those manual adding new range and tab to simple formula; because I try;

=QUERY("{"&TEXTJOIN(";",TRUE,SHEETLIST!B7:B)&"}","SELECT * where Col9 is not null",0)

but it didn't work.

Thanks Nico

Your Answer

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

Browse other questions tagged or ask your own question.