You may sometimes want to lock (freeze) a cell in the source for the IMPORTRANGE function to always point to specific cell content.

When rows or columns are added or removed in the source, this ensures the imported value remains unchanged, unaffected by such modifications.

Normally, locking specific cell content is impossible with the IMPORTRANGE function in Google Sheets because it uses a range string, and the source data is in a different file (workbook). However, there is a workaround for this issue.

We can freeze or lock cell content, or even a range, in IMPORTRANGE by using Named Ranges in Google Sheets. Let’s explore the details.

Locking Cell Content in Google Sheets IMPORTRANGE – Why Is It Needed?

Locking or freezing cell content in IMPORTRANGE is essential when importing a specific cell value, usually, a cell containing a total or aggregated value.

In this example, cell H13 contains the sum of values in the range H2:H12.

You can easily import this cell value into another worksheet using the following generic IMPORTRANGE formula:

=IMPORTRANGE("spreadsheet_url", "sheet_name!H13")

However, deleting or inserting a row between H2:H12 in the source sheet causes the total in H13 to move.

For instance, if you delete one row, the total moves to H12, but the IMPORTRANGE formula continues importing from cell H13.

This happens because the IMPORTRANGE function uses a static range_string to import data.

Syntax: IMPORTRANGE(spreadsheet_url, range_string)

Let’s solve this issue using a workaround that involves Named Ranges.

Solution

Refer to the screenshot above, where the total is in cell H13. To lock this cell’s content, we need to name cell H13. Navigate to cell H13 in the source sheet, then click Data > Named Ranges > Add a range.

Name the cell Total.

Note: “OFFSET Dynamic” is the name of my source sheet that contains the value to import.

Now, open the sheet where you want to import the value of cell H13 and apply the IMPORTRANGE formula as follows:

Formula 1:

=IMPORTRANGE("spreadsheet_url", "Total")

This is a generic formula, so you’ll need to replace spreadsheet_url with the actual URL of the source sheet. The range_string should be the named range, which in this case is “Total.”

Doing this lets you lock or freeze a cell’s content in an IMPORTRANGE formula.

Similarly, you can lock content in a range of cells by naming the range. For example, you can name the range H2:H12 and use that in IMPORTRANGE.

Formula 2:

=SUM(IMPORTRANGE("spreadsheet_url", "Total"))

Pros and Cons of Locking Cell Contents in IMPORTRANGE Using Named Ranges

Pros:

For both Formula 1 and Formula 2:

• Adding or removing rows or columns in the IMPORTRANGE source won’t affect the result.
• The named range automatically adjusts to ensure the correct cell value or range is imported.

Cons:

For Formula 2:

• If you change the function in H13, for example from SUM to AVERAGE, it won’t be reflected in the above Formula 2 because we are importing the range H2:H12 separately and then summing it.

Resources

• Combine Multiple Sheets with IMPORTRANGE in Google Sheets
• Dynamic Sheet Names in IMPORTRANGE in Google Sheets
• How to Control Reloading of IMPORTRANGE from the Source File in Google Sheets
• How to Use IMPORTRANGE Function with Conditions in Google Sheets
• IMPORTRANGE Named Ranges in Google Sheets
• IMPORTRANGE Result Too Large Error: Solution
• IMPORTRANGE to Import Visible Rows in Google Sheets
• Relative Cell Reference in IMPORTRANGE in Google Sheets