0

In Google Sheet, I'm using a rather large array of queries to select data from X number of Sheets. The number of Sheets is unknown, and manually entered into a list from which the queries pick up their ranges using INDIRECT. In the formula, it looks like this: indirect("'" & Data!E2 & "'!$A$1:$T" = ABC!$A$1:T.

The IFERROR and {""/""/...} handles tabs that haven't yet been created, and ignores those queries.

On each Sheet (ABC, DEF, etc.; formatted the exact same way), there can be many lists of people. The Search Pool tab merges all the data into one long list of Persons searchable by search field (Col1 contains '" &M3& "').

In the Data tab, I can list a number of Responsibles (ABC, DEF, etc.) when I need to create new Responsibles, and then create a new tab for a Responsible.

This all technically works as intended. I'm just wondering if there's a more effective way to do it. It's currently a bit slow and unwieldy.

={
iferror(QUERY(indirect("'" & Data!E2 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E3 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E4 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E5 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E6 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E7 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E8 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E9 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E10 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E11 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E12 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E13 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E14 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E15 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E16 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E17 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E18 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E19 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E20 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(indirect("'" & Data!E21 & "'!$A$1:$T");"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'");{""\""\""\""\""\""\""\""\""\""\""})

} ```

1 Answer 1

0

You can simplify it with REDUCE to stack all the same queries. And, inside the QUERY, given that you only need the first 11 columns, you may simplify also the inside QUERY:

=REDUCE(TOCOL(,1),TOCOL(Data!E2:E,1),LAMBDA(a,v,
IFERROR(VSTACK(a,QUERY(indirect("'" & v & "'!$A$1:$K"),"SELECT * WHERE Col2 is not null AND Col1 != 'Consultant' AND Col1 contains '" &M3& "'")))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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