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

This guide will explain how to reference a list of tab names in a Google Sheets query.

Given a Google Sheets spreadsheet containing data separated into multiple tabs or sheets, you may want to use the QUERY function on all data at once.

While it is possible to hard-code the tab names into the query function, we can use a more dynamic formula that can be easier to work with in the future. Using a custom named function called REFERENCE_TAB_NAMES, we can give the user control of which sheets to include in the query.

In this guide, we will provide a step-by-step tutorial on how to use this custom function to reference a list of tab names in a query in Google Sheets.

The Anatomy of the REFERENCE_TAB_NAMES Function

The syntax of the named function REFERENCE_TAB_NAMES is as follows:

=REFERENCE_TAB_NAMES(tab_names, y_n, data_range)

Let’s look at each argument to understand how to use the REFERENCE_TAB_NAMES function.

• REFERENCE_TAB_NAMES() refers to the named function of the same name. This function returns data given a specified range and a list of selected tab names.       
• tab_names argument refers to the range in the current sheet that contains all the tab names the user might need to pull data from.
• y_n argument refers to the range in the current sheet that determines which tabs to pull data from. The data in this range must be a boolean value or a checkbox element.
• data_range argument is a string value that represents the cell range to use when pulling data from any selected tabs.

A Real Example of Referencing a List of Tab Names in a Query in Google Sheets

Let’s explore a simple example where we can use the REFERENCE_TAB_NAMES function to reference data from multiple tabs in a QUERY formula.

Suppose we have a Google Sheets document where each sheet tab contains monthly expense data. For example, the sheet “Jan” will contain expenses tracked during that specific month.

We want to use the QUERY function to sort and filter through the expense data from these different tabs.

In the sheet above, we have a cell range containing all the tab names and a corresponding range where the user can select whether to include that tab’s data in the query.

Columns D and E now contain all the expense data from all selected tabs sorted from most expensive to least expensive. To do this, we can use the following formula:

=QUERY(REFERENCE_TAB_NAMES(A2:A4,B2:B4,"A2:B7"),"Select * order by Col2 DESC",0)

Let’s try to understand what the formula above tries to accomplish. 

The formula REFERENCE_TAB_NAMES(A2:A4,B2:B4,"A2:B7") looks at the tab names in the range A2:A4 and retrieves data from each tab that lie on the range A2:B7. The formula uses the second argument to limit which tabs to pull data from. The output of this function should be a range of cells containing consolidated data from the specified tabs.

We’ll then use the result of this function as the first argument of the QUERY function. We’ll then use the query “Select * order by Col2 DESC” to sort the consolidated data in descending order by expense amount.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to reference a list of tab names in a Google Sheets QUERY function.

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

1. First, let’s create a new named function that we’ll call to reference a list of tab names.
   
   To do this, select Data > Named functions.
2. Next, we’ll need to enter the function name, arguments, and formula definition.
   
   Our named function uses the following formula: =ArrayFormula(ifna(query(iferror(split(flatten(split(byrow(filter(tab_names,y_n),lambda(rr, textjoin(“###”,true,byrow(indirect(join(“”,rr,”!”&data_range)),lambda(r,join(“***”,r)))))),”###”)),”***”,false,false)),”Select * where Col1 is not null”)))
3. Next, create a range in your sheet containing a list of tab names and a corresponding checkbox element. The checkbox will be used to determine whether REFERENCE_TAB_NAMES will pull data from that specific tab.
   
   
4. Next, we’ll type out the REFERENCE_TAB_NAMES function and add the required arguments.
   
   In this example, we’ll set the range with our tab names as A2:A4 and the corresponding range of checkboxes as B2:B4. Lastly, we’ll also specify we want to retrieve data from cells A2:B7 from each tab.
   
   Checking or unchecking any of the tab names will dynamically change the output of our formula
5. We can wrap the REFERENCE_TAB_NAMES function with a QUERY to further filter, group, and sort the consolidated data.
   

To learn more about querying in Google Sheets, you can read our post on how to query with multiple criteria in Google Sheets.

That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!