If you have multiple sheets in a Google Sheets workbook and want to import data from different sheet tabs into another spreadsheet, you can make the sheet name in IMPORTRANGE dynamic using a drop-down list.
Since the sheet name is part of the range string, you don’t need the INDIRECT function. Instead, simply concatenate the cell containing the sheet name with the range reference.
This lets you switch the imported data by simply selecting a different sheet name from the drop-down, without editing the formula.
Use the following formula:
=IMPORTRANGE(spreadsheet_url, A1&"!A1:C")
How to Use Dynamic Sheet Names in IMPORTRANGE
Before we go through an example, let’s quickly review the syntax of the IMPORTRANGE function.
IMPORTRANGE(spreadsheet_url, range_string)The spreadsheet URL is the URL of the source spreadsheet. You can copy it from your browser’s address bar. It doesn’t matter which sheet tab is open when you copy the URL; any sheet tab URL from the source spreadsheet will work.
The range string specifies both the sheet tab and the cell range to import. If you specify only a range, such as "A1:C", without a sheet name, IMPORTRANGE imports the range from the first sheet tab in the source spreadsheet.
If the order of the sheet tabs changes later, the formula will import data from whichever sheet becomes the first tab.
To prevent this behavior, include the sheet name in the range string, for example, "January!A1:C".
If you concatenate the sheet name with the range reference and retrieve the sheet name from a drop-down list, you can switch between sheet tabs without editing the formula. This creates a dynamic sheet reference in IMPORTRANGE.
Example
Suppose your source spreadsheet contains three sheet tabs named Jan, Feb, and Mar, each with data in A1:C. In another spreadsheet, you want to display the data from the sheet selected in a drop-down in cell A1.
Follow these steps:
- Create a drop-down list in cell A1 containing the sheet names Jan, Feb, and Mar. You can do this by selecting Insert > Drop-down.
- Open the source spreadsheet and copy its URL from your browser’s address bar.
- Return to the destination spreadsheet.
- In cell B1, enter the following formula:
=IMPORTRANGE(spreadsheet_url, A1&"!A1:C")- Replace
spreadsheet_urlwith the copied spreadsheet URL, keeping it enclosed in double quotes.
Tip: The first time you use IMPORTRANGE, Google Sheets will ask you to Allow access between the two spreadsheets.
That’s it! Whenever you select a different sheet name from the drop-down in A1, the IMPORTRANGE formula automatically imports data from the corresponding sheet tab.
Frequently Asked Questions
Do I need to use the INDIRECT function with IMPORTRANGE?
No. The IMPORTRANGE function does not require INDIRECT to make the sheet name dynamic.
The second argument, range_string, is a text string. If the sheet name is stored in a cell, simply concatenate it with the range reference. There’s no need to wrap the cell reference in INDIRECT.
What happens if I use only a range reference without a sheet name in IMPORTRANGE?
If you specify only a range, such as "A1:C", IMPORTRANGE imports data from the first sheet tab in the source spreadsheet.
If the order of the sheet tabs changes later, the formula will import data from whichever sheet becomes the first tab.
What happens if the sheet name drop-down is empty?
If the cell containing the sheet name is blank, the formula returns a #N/A error with the message “Internal error” because the generated range string is invalid (for example, "!A1:C"). Select a valid sheet name from the drop-down to resolve the error.
Conclusion
Using a dynamic sheet name in IMPORTRANGE lets you import data from different sheet tabs without editing the formula. Simply combine a drop-down list with the sheet name in the range_string argument to create a flexible and easy-to-maintain solution.
Omggg
Thank you very much for this topic. I was searching for this everywhere on google.
In my requirement, I need to use a single formula to fetch values from file B across all sheets. Here I couldn’t use dropdown bcoz I need to see data from all 3 sheets at a time.
I mean 1st row may have data from sheet ‘Jan’. 2nd row may have data from ‘Feb’ and so. Is there any solution for this?
Kindly help. Thanks in advance
I forgot to mention that I am using Vlookup function to fetch data from File B based on unique code, code for each item in Jan, Feb, Mar sheets.
I am using the Importrange function inside Vlookup.
Hi, Divya,
Please wait for my next tutorial. A similar tutorial is already in the pipeline. In that, I won’t write the Vlookup part since it’s already explained.
1. Align Imported Data with Manually Entered Data in Google Sheets.
2. How to Vlookup Importrange in Google Sheets [Formula Examples].