There’s a misconception about using the Indirect function to get dynamic sheet names in Importrange in Google Sheets. Many users attempt to use the Indirect function as the range_string in Importrange, only to encounter an error. So, what’s the solution? Let’s dive in and find out.
What Are Dynamic Sheet Names in the Importrange Function?
To understand this concept, let’s consider a simple example. Imagine you have a Google Sheets workbook with three sheets named “Jan,” “Feb,” and “Mar.” Now, you want to selectively import data from these sheets into another Google Sheets workbook.
The Importrange function allows you to achieve this by controlling the sheet names. You can use a dropdown menu to select the sheet from which you want to import the data. The Importrange formula, located in cell B1, dynamically imports data based on the selected option in the dropdown menu.
How to Get Dynamic Sheet Names in Importrange
To obtain dynamic sheet names in the Importrange function, follow these steps:
-
Create a Data Validation dropdown menu that contains the sheet names you want to import. You can do this by going to the Insert menu and selecting “Drop-down.”
-
Let’s take a look at the Importrange function syntax. We will be playing with the range_string argument in the formula:
IMPORTRANGE(spreadsheet_url, range_string). -
In cell B1, enter the following formula:
=IMPORTRANGE("enter_spreadsheet_url_here",A1&"!A1:C")Note: Replace “enter_spreadsheet_url_here” with the actual URL of the workbook you’re importing from. This formula makes the sheet name dynamic, while the range reference remains fixed at A1:C.
-
If you want to add more dynamism to the formula and control the range as well, create another dropdown menu.
-
To do this, start by creating Named Ranges in the source file. Go to each sheet tab and create a Named Range for the desired range of cells. For example, in the first sheet tab (Jan), create a Named Range called “table1” for range A1:C.
-
Continue this process for the other sheet tabs, giving each Named Range a unique name (e.g., “table2” for range A1:C in the second sheet tab).
-
Once you’ve created the Named Ranges, go back to the sheet with the dropdown menu and Importrange formula.
-
Create another dropdown menu in cell A2 containing the named ranges you just created.
-
Modify the formula in cell B1 as follows:
=IMPORTRANGE("enter_spreadsheet_url_here",A1&"!"&A2)Now, the formula is responsive to both sheet names and ranges.
That’s it! Now you know how to bring dynamism to the Importrange function in Google Sheets. Enjoy exploring the possibilities! To learn more about Google Sheets and other useful tips, visit Crawlan.com.
