The Importrange function in Sheets works well with Named Ranges. See how to Importrange Named Ranges in Google Sheets.

The Importrange function is useful to import data in one workbook to another. I mean it’s the function to use to make data transfer between two Google Sheets files.

IMPORTRANGE(spreadsheet_url, range_string)

The Importrange function requires the URL of the source sheet and range to import (range_string). You can replace the range_string in Importrange with Named Ranges.

One more thing! If you grab the URL of someone else’s Google Sheets and apply it in Importrange, it won’t work. You need permission to use the URLs in Importrange.

If you have the permission then you can “Allow Access” in the cell that contains the formula which normally returns a #REF! error in its first use.

How to Importrange Named Ranges in Google Sheets

I am talking about two workbooks, I mean two Google Sheets files, here.

File A: Contains Two Tabs named as “Div A” and “Div B”

I have the following contents in the Tab “Div A” in the file named as “File A”. Assume this as the attendance of students in a class in Division A.

In Tab 2 which is “Div B” in the same file, I have similar data. It’s not relevant here. So I am not sharing the screenshot.

File B: A Blank Workbook Contains the Tab “Sheet1”

To import the data in this workbook (“File B”) from another workbook (“File A”) we can use the Importrange function as below.

=importrange("https://docs.google.com/spreadsheets/d/1fuzAP1Yg2NRwxumDkDicrjT5nmUNyE-JtrwObXdZqeg/edit#gid=0","Div A!A1:K7")

I have this formula in cell A1 in “Sheet1” (“File B”). This formula imports the data in the range A1:K7 that available in “File A”.

Now let me show you how to Importrange Named Ranges in Google Sheets.

Define Named Ranges to Use in Importrange in Google Sheets

Go to “File A” (tab “Div A”) and name the range A1:K7. To do that highlight to select the range A1:K7 and right click and choose “Define named range”.

On the sidebar panel, replace “NamedRange1” with “Div_A”. Similarly, create a named range for the range in the tab “Div B” as “Div_B”.

Importrange + Named Range Combination Formula

Once you have defined two named ranges as above, go to “File B”. Open a new tab “Sheet2” and in cell A1, enter this formula.

=importrange("https://docs.google.com/spreadsheets/d/1fuzAP1Yg2NRwxumDkDicrjT5nmUNyE-JtrwObXdZqeg/edit#gid=0","Div_A")

In this formula, the range_string is replaced by the named ranges.

What is the benefit of using Named Ranges in Importrange in Google Sheets?

Advantages of Using Named Ranges in Google Sheets Importrange Function

To understand the benefit of using named ranges in Importrange do as follows.

Benefit of Using Named Range in Importrange 1

Got the workbook “File A”. In “Div A” tab, select the column A. Then right click and choose “insert 1 right”.

In “File B”, check the formula outputs in “Sheet1” (formula without named ranges) and “Sheet2” (formula with named ranges). You can see that in Sheet1, the imported data missed one column at the last.

That means Named Ranges brings dynamism to the ranges in Importrange.

Benefit of Using Named Range in Importrange 2

You can refer to a Data Validation drop_down menu, that contains the Named Ranges, in Importrange.

I didn’t get. Can you explain?

Yes! Create one more Sheet (“Sheet3”) in “File B”. Right click on cell A1 and choose “Data validation”.

Choose “List of items” as Criteria and enter the name of Named Ranges as comma separated values in the given field as below.

Div_A,Div_B

In Cell B1 enter this formula.

=importrange("https://docs.google.com/spreadsheets/d/1fuzAP1Yg2NRwxumDkDicrjT5nmUNyE-JtrwObXdZqeg/edit#gid=0",A1)

Hope you find this tutorial, i.e., how to Importrange Named Ranges in Google Sheets, worthy. Enjoy!