The IMPORTRANGE function doesn’t support relative cell references because the range_string argument is text. However, you can make the range dynamic by generating the range_string using the ADDRESS, ROW, and COLUMN functions. This allows the imported range to change automatically when you copy the formula down or across.

Why Relative Cell References Don’t Work in IMPORTRANGE

Suppose you have the following IMPORTRANGE formula in cell A1.

Formula 1:

=IMPORTRANGE("URL", "Sheet1!A2:F2")

Note: Replace "URL" with the full URL of your source spreadsheet.

When you drag this formula down, the imported range doesn’t change to A3:F3. Instead, Google Sheets copies the formula as it is.

This happens because the second argument of IMPORTRANGE, range_string, is text.

IMPORTRANGE(spreadsheet_url, range_string)

Since "Sheet1!A2:F2" is a text string, Google Sheets doesn’t treat it as a cell reference. As a result, relative references don’t work automatically.

Fortunately, there is a simple workaround. Instead of typing the range manually, you can generate it with a formula.

How to Get Relative Cell References in IMPORTRANGE

We can replace the hard-coded range_string with a dynamic formula. The idea is to generate the starting and ending cell references and then combine them into a valid range.

We’ll use the following functions:

• ADDRESS
• ROW
• COLUMN
• JOIN

Let’s start by generating the first cell reference.

Step 1: Generate the Starting Cell Address

Enter the following formula in any blank cell.

=ADDRESS(ROW(A2), COLUMN(A2),,,"Sheet1")

Replace "Sheet1" with the name of your source sheet.

The formula returns:

Sheet1!$A$2

Step 2: Generate the Ending Cell Address

Now generate the last cell in the range.

=ADDRESS(ROW(F2), COLUMN(F2))

Result:

$F$2

Step 3: Create the Range String

Now combine both addresses using JOIN.

=JOIN(":", ADDRESS(ROW(A2), COLUMN(A2),,,"Sheet1"), ADDRESS(ROW(F2), COLUMN(F2)))

The formula returns:

Sheet1!$A$2:$F$2

This is the dynamic range_string that we’ll use in IMPORTRANGE.

Step 4: Use the Dynamic Range in IMPORTRANGE

Replace the hard-coded range_string in Formula 1 with the formula from the previous step.

=IMPORTRANGE("URL", JOIN(":", ADDRESS(ROW(A2), COLUMN(A2),,,"Sheet1"), ADDRESS(ROW(F2), COLUMN(F2))))

That’s all!

When you copy the formula down, the imported range changes automatically.

• First row: Sheet1!$A$2:$F$2
• Next row: Sheet1!$A$3:$F$3
• Then: Sheet1!$A$4:$F$4

In other words, you get relative cell references in IMPORTRANGE.

Can You Copy the Formula Across?

Yes, but only when you’re importing one column at a time.

For example, suppose you’re importing A2:A10. In that case, use the following formula to generate the range.

=JOIN(":", ADDRESS(ROW(A2), COLUMN(A2),,,"Sheet1"), ADDRESS(ROW(A10), COLUMN(A10)))

When you copy the IMPORTRANGE formula to the right, the imported range changes to Sheet1!$B$2:$B$10, Sheet1!$C$2:$C$10, and so on.

How to Import a Single Cell Using Relative Cell References

The previous example imports a range of cells. If you only want to import a single cell and make the reference relative, you don’t need to create a range string.

You can use the output of the ADDRESS function directly as the range_string.

For example:

=IMPORTRANGE("URL", ADDRESS(ROW(A2), COLUMN(A2),,, "Sheet1"))

When you copy the formula down, the imported cell changes from Sheet1!A2 to Sheet1!A3, Sheet1!A4, and so on. Likewise, when you copy the formula across, the column reference changes automatically.

When to Use This

Use this approach when you want each copied IMPORTRANGE formula to import a different row or column from the source sheet.

For example, if you copy the formula down, you may want it to import the next row automatically. Likewise, if you copy it across, you may want it to import the next column.

If your goal is simply to import adjoining rows or columns, you don’t need this technique. You can import the entire range in a single IMPORTRANGE formula instead.

Frequently Asked Questions

Can IMPORTRANGE use relative cell references?

No. The range_string argument is text, so relative references don’t work directly. You must generate the range string dynamically.

Why doesn’t the imported range change when I copy the formula?

Because IMPORTRANGE treats "Sheet1!A2:F2" as text. Google Sheets copies the same text unless you build it using functions like ADDRESS, ROW, and COLUMN.

Can I use this method to import the next column?

Yes. When importing a single column, the same technique works when copying the formula across columns.

Related Resources

• How to Lock Cell Content in IMPORTRANGE in Google Sheets
• Dynamic Sheet Names in IMPORTRANGE in Google Sheets
• IMPORTRANGE Named Ranges in Google Sheets – A Complete Guide