We can combine multiple sheets in the Importrange function in Google Sheets and also control them with the help of a drop-down menu.

In the context of this tutorial, multiple sheets can be;

• Two or more sheets (tabs) from a Google Sheets file.
• Sheets (tabs) from two or more Google Sheets files.

Whichever the case, in the combined Importrange, the steps will be the same to follow.

Further to have greater control over the import, I’ll use a drop-down menu. With the help of the menu, we can import either of the files/sheets or all as combined.

That means within one sheet you can see the data from all the files/sheets individually or combined.

For example, I have three Google Sheets files named as “Zone 1”, “Zone 2”, and “Zone 3”. In each file, I have a tab named as “Week 1”, “Week 2”, and “Week 3” respectively.

Let me show you how to import and combine these multiple sheets (files) using the Importrange function in Google Sheets.

In a fourth file, with the help of a drop-down menu and a combined Importrange formula, you can either import data from “Zone 1” (tab name is “Week 1”), “Zone 2” (tab name is “Week 2”), or “Zone 3” (tab name is “Week 3”) or all these three files as combined.

Data in “Zone 1”

Data in “Zone 2”

Data in “Zone 3”

Importrange Output in the Fourth File Controlled by Drop-Down Menu

Drop-Down Menu to Select Individual or Combined Importrange Files

Let’s see how to combine multiple sheets in Importrange in Google Sheets and also control via a drop-down menu as above.

List of URLs and Sheets

First of all, let’s prepare a list of URLs and tab names to import.

In a fourth file, in which you want to import multiple Google Sheets files as combined or individual, do as follows.

• Enter the tab names from each files one by one in A1:A3 range and their corresponding file URLs in B1:B3.
• Then in cell A4 enter the text “Combined” which will be later used in the drop-down to combine multiple sheets in the Importrange.
• In cell C1, enter the following formula.

=ArrayFormula("'"&A1:A3&"'!A2:C")

What does this simple array formula do?

We have the tab names from all the sheets in column A (except “Combined”), right?

If the tab name contains white space, a single apostrophe to be inserted at the beginning and end of the tab name while using it as a reference in formulas.

I have used the ampersand sign (concatenate equivalent) in the array formula to join the said apostrophe character and an exclamation sign.

For example, the tab name Week 1 has a white space character. So use it as 'Week 1'! then followed by range. Even if there is no white space, you can insert apostrophe as above.

To understand what is A2:C in the formula, see the 3 tables (at the beginning). It’s the range to import from each sheet.

My data is in the range A1:C. But I have used A2:C in the just above formula to avoid the header row. Because when we combine multiple sheets in Importrange we don’t want to repeat the header row.

How to Combine Multiple Sheets in Importrange

Assume the above formula is in ‘Sheet1’ in the fourth file. In ‘Sheet2’ in that file, in cell A1, create a drop-down as below.

Steps:

• Open the Data validation dialogue box from the Data menu.
• Enter the following values (as per the screenshot).

Since we are not importing the header rows type it manually in B1:D1 (yellow highlighted cells).

Link Drop-Down with Spreadsheet_URL and Range_String Using Vlookup

The following Vlookup in cell B2 will use the drop-down item, except “Combined”, as its search key and return the correct URL from ‘Sheet1’.

Vlookup 1:

=vlookup(A1,Sheet1!A1:C4,2,0)

To get the corresponding tab name and range, use this Vlookup. No need to enter this formula in any cell. I’ll tell how to use it.

Vlookup 2:

=vlookup(A1,Sheet1!A1:C4,3,0)

As per the Importrange syntax, we have now spreadsheet_URL (Vlookup 1) and the range_string (Vlookup 2).

IMPORTRANGE(spreadsheet_url, range_string)

Here it is in a formula form in cell B2.

=importrange(vlookup(A1,Sheet1!A1:C4,2,0),vlookup(A1,Sheet1!A1:C4,3,0))

The above Importrange formula will return data from “Zone 1”, “Zone 2”, or “Zone 3” based on the selection of the menu item which is “Week 1”, “Week2”, or “Week 3” respectively.

Then how to combine the multiple sheets, i.e. “Zone 1”, “Zone 2” and “Zone 3”, in Importrange in Google Sheets?

For that, we can use a combined Importrange formula.

Combining Multiple Google Sheets Files to Import Using Importrange

Select the item “Combined” in the drop-down. The just above formula would return an #N/A error.

Here we can make use of the IFNA function. Find the syntax of this function below.

IFNA(value, [value_if_na_error])

In this, the ‘value’ argument is the above Importrange formula. The ‘value_if_na_error’ is the following combined multiple sheets Importrange formula (a quick explanation given after a few lines below).

Combined Importrange Formula:

=query({importrange(Sheet1!B1,Sheet1!C1);importrange(Sheet1!B2,Sheet1!C2);importrange(Sheet1!B3,Sheet1!C3)},"Select * where Col1 is not null")

The Final Formula in cell B2 will be as below.

=ifna(importrange(vlookup(A1,Sheet1!A1:C4,2,0),vlookup(A1,Sheet1!A1:C4,3,0)),query({importrange(Sheet1!B1,Sheet1!C1);importrange(Sheet1!B2,Sheet1!C2);importrange(Sheet1!B3,Sheet1!C3)},"Select * where Col1 is not null"))

Here is a quick explanation of the above combined Importrange formula.

Write three Importrange formulas and separate them with semicolons. Put them inside curly brackets. It’s like;

={importrange(Sheet1!B1,Sheet1!C1);importrange(Sheet1!B2,Sheet1!C2);importrange(Sheet1!B3,Sheet1!C3)}

The outer Query is to remove blank rows.

Conclusion

In my example, I have imported data from 3 Google Sheets files in combined as well as individual forms. But how to combine multiple sheets from a single file in Importrange in Google Sheets?

Just simple! Instead of using separate URLs in column B in ‘Sheet1’ (see the second screenshot from the top), use the same URL in all the rows.

Importrange Resources

• How to Freeze Cell in Importrange in Google Sheets [Lock Cell Reference].
• How to Use IMPORTRANGE Function with Conditions in Google Sheets.
• Dynamic Sheet Names in Importrange in Google Sheets.
• How to Vlookup Importrange in Google Sheets [Formula Examples].
• How to Use Query With Importrange in Google Sheets.
• Importrange Named Ranges in Google Sheets.
• Dynamic Column Id in Query Importrange Using Named Ranges.
• Relative Cell Reference in Importrange in Sheets.
• Dynamic Column in Vlookup Importrange Formula in Google Sheets.
• Sumif Importrange in Google Sheets – Examples.
• IMPORTRANGE to Import Visible Rows in Google Sheets.
• How to Control Reloading of Importrange from the Source File in Google Sheets.