Combine Multiple Sheets in Importrange and Control Via Drop-Down

Have you ever wanted to combine multiple sheets in Google Sheets and control them with a drop-down menu? Well, you’re in luck! In this tutorial, I will show you how to do just that.

Introduction

With Google Sheets, you can easily combine multiple sheets from different files into one and have complete control over which sheets to import. This feature is incredibly useful when you have data spread across several sheets and want to view them all in one place.

In this tutorial, we will focus on combining two or more sheets from either a single Google Sheets file or multiple Google Sheets files. Regardless of the case, the steps to combine the sheets using the Importrange function will remain the same.

To make things even more convenient, we will use a drop-down menu to control the import process. With the help of this menu, you can choose to import data from specific files or sheets, or even combine all of them into one view.

Let’s dive in and see how it works!

Drop-Down Menu to Select Individual or Combined Importrange Files

Before we start, we need to prepare a list of URLs and sheet names which we want to import. In a fourth file, follow these steps:

1. Enter the tab names from each file, one by one, in range A1:A3.

2. Enter the corresponding URLs of the sheets in range B1:B3.

3. In cell A4, enter the text “Combined”. This will be used later in the drop-down menu to combine multiple sheets.

4. In cell C1, enter the following formula:

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

What does this formula do? It simply combines the sheet names with the appropriate range, using single apostrophes to handle tab names with white spaces. For example, if a tab name is “Week 1”, it will be referenced as 'Week 1'!A2:C.

How to Combine Multiple Sheets in Importrange

Now that we have the link between URLs, sheet names, and ranges, let’s see how to combine them using the Importrange function.

Assuming that the formula we discussed above exists in ‘Sheet1’ of the fourth file, follow these steps:

1. In ‘Sheet2’ of the same file, create a drop-down menu in cell A1.
2. Open the Data Validation dialogue box from the Data menu.
3. Enter the following values as per the screenshot.
   • List from Range: Sheet1!$A$1:$A$4
   • Criteria: Cell Range: B1:D1
   • Criteria: Criteria: <>""

Since we don’t want to import the header rows, manually type them in cells B1:D1.

Link Drop-Down with Spreadsheet_URL and Range_String Using Vlookup

Now, let’s link the drop-down menu with the appropriate spreadsheet URLs and range strings using the Vlookup function.

The following Vlookup formula in cell B2 will use the drop-down item 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 formula:

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

As per the Importrange syntax, we now have the spreadsheet URL (Vlookup 1) and the range string (Vlookup 2). We can use them to retrieve the data.

The final formula in cell B2 will be:

=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"))

In this formula, we handle the scenario where the drop-down menu item is set to “Combined” by using the IFNA function. If the drop-down item is not “Combined”, it will use the Importrange formula to import data from the selected sheet. Otherwise, it will use a combined Importrange formula to import data from all the sheets.

Conclusion

Combining multiple sheets in Google Sheets and controlling them with a drop-down menu is a fantastic way to organize and view your data efficiently. This method allows you to have total control over which sheets to import and how to combine them.

In the example provided, we imported and combined data from three Google Sheets files. However, the same method can be applied to combine sheets from a single file as well.

Now that you know how to combine multiple sheets using Importrange and control them with a drop-down menu in Google Sheets, you can easily manage and analyze your data with ease.

For more tutorials and tips on Google Sheets and other exciting topics, visit Crawlan.com. Happy sheeting!