With the help of the Lambda helper functions, we can now reference a list of tab names in a Query formula in Google Sheets.

Earlier, we were hardcoding the tab names within the Query formula as below.

=Query({Jan!A2:D100;Feb!A2:D100;Mar!A2:D100;Apr!A2:D100},"Select sum(Col2) where Col1 ='Product 6'")

We have already seen that in my tutorial titled How to Include Future Sheets in Formulas in Sheets.

It was because the Indirect function in Google Sheets only supports pulling data from a single sheet at a time in a workbook (the said behavior is not yet changed).

For example, enter the tab name “Jan” in cell A2 and pull data from that sheet in the following way.

=Query(indirect(A2&"!A2:D100"),"Select....

You can read more about that here – Google Sheets: Dynamic Sheet Tab Names in Formulas.

But it won’t support reference to a list of tab names in A2:A6 and pull data from all of that sheets in the same fashion.

We can now make that, i.e., Indirect multiple sheets, possible using the new features in Google Sheets.

We can create a named function called REF_SHEET_TABS() for that from a native Google Sheets formula or use that formula itself.

Reference a List of Tab Names in Query and Benefits

Reference a list of tab names in Query is very useful in many ways, and here are the most important ones among them.

1. It helps users to include or exclude multiple sheets in a Query formula without modifying it each time.
2. Include future sheets in a Query formula by adding those tab names within the list (When using my custom function, do not check the tick boxes against the future tab names).

In the above example, “Jan,” “Feb,” and “Mar” are the three existing tab names in one of my Google Sheets files. They contain data in A2:D.

“Apr” and “May” are my predefined names for future sheets, so they do not exist.

In another sheet named “Main” in the same file (workbook), I have a list of tab names in column A and checkboxes in column B.

The REF_SHEET_TABS formula in cell D2 pulls data from all three existing sheets when selecting the corresponding checkboxes.

When the future sheets are ready, we can pull data from them by selecting the tick boxes against their names in the list.

Wrap it with Query to aggregate or pivot the pulled data from multiple sheets in one go!

How to reference a list of tab names as above in Query? Please read on to learn that.

Function to Reference a List of Tab Names in a Query or Other Formulas in Google Sheets

As I mentioned above, we can use a native Google Sheets formula or a custom-named function. Let’s start with the function first.

First, make a copy of my sample Sheet from the end of this tutorial.

Then follow the instructions in my NAMED FUNCTION tutorial to import the REF_SHEET_TABS named function from my copied sheet to yours.

Syntax of the REF_SHEET_TABS Named Function in Google Sheets:

REF_SHEET_TABS(list, y_n, range)

Arguments:

list: The one-dimensional array containing the tab names.

y_n: The one-dimensional array containing Tick Boxes.

This helps users to dynamically include or exclude sheets when referencing the list of tab names in a Query or other aggregate/lookup formulas. The list and y_n must be of the same size (must contain a single column and an equal number of rows).

range: The data range in tabs as a string, e.g., "A2:D100" .

How do we use the REF_SHEET_TABS named function to reference a list of tab names in Query?

As per the above GIF, the formula in cell D2 is as follows.

=REF_SHEET_TABS(A2:A,B2:B,"A2:D")

If you want to reference a list of tab names and aggregate data in QUERY, we can follow the below example.

=query(REF_SHEET_TABS(A2:A,B2:B,"A2:D"),"Select Col3, sum(Col4) where Col3 is not null  group by Col3 label sum(Col4)''",0)

Usage Notes

If you have names of future sheets in your reference list of tab names, do not accidentally tick the boxes against them. The ideal way is not to insert tick boxes against them.

If you have already written a Query using the REF_SHEET_TABS function and accidentally tick a future tab, your formula won’t recognize it once you insert the tab in question in the future.

In that scenario, you may copy the formula, delete it from the cell in question, and paste it again.

Formula to Reference a List of Tab Names in Query in Google Sheets

Use the below native formula, if you don’t want to use my REF_SHEET_TABS function to reference a list of tab names in Query in Google Sheets.

=LET(list,A2:A,y_n,B2:B,range,"A2:D",ARRAYFORMULA(LET(data,REDUCE(SUBSTITUTE(if(,,),"",if(,,),SEQUENCE(1,columns(indirect(range)))),FILTER(list,y_n)&"!"&range,LAMBDA(a,v,VSTACK(a,INDIRECT(v)))),FILTER(data,LEN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(data),,9^9))))))))

Replace A2:A with the range that contains the reference list of tab names, B2:B with the corresponding check box range, and “A2:D” with the data range.

Wrap it with Query as earlier to summarize or aggregate the data.

Step-by-Step Formula Explanation

Here is the base formula. It works equally well but won’t filter out blank rows.

But using it will be easier to understand the logic and formula.

=ArrayFormula(reduce(substitute(if(,,),"",if(,,),sequence(1,4)),filter(A2:A6,B2:B6)&"!A2:D10",lambda(a,v,vstack(a,indirect(v)))))

I’ve used the REDUCE function to code a formula to refer to a list of tab names in Query.

REDUCE (Syntax): REDUCE(initial_value, array_or_range, lambda)

Where;

initial_value : substitute(if(,,),"",if(,,),sequence(1,4))

It returns four truly blank cells.

The number of blank cells depends on the number of columns in the range.

If your range has ten columns, replace 4 with 10 to return ten truly blank cells horizontally.

array_or_range : filter(A2:A6,B2:B6)&"!A2:D10"

This FILTER filters out the unchecked tab names. See the range added to it.

lambda : lambda(a,v,vstack(a,indirect(v))))

a = initial_value

v = array_or_range

It’s the lambda that applies to each tab name in the array_or_range. The VSTACK in the lambda appends the range in each tab vertically.

That’s all about how to reference a list of tab names in Query in Google Sheets.

REF_SHEET_TABS_II