How to Lock Cell Content in IMPORTRANGE in Google Sheets

Published on

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.

Sample Data for Locking Cell Content in IMPORTRANGE in Google Sheets

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.

Naming a Cell to Lock Its Content

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"))
Naming a Range to Lock Its Content

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

3 COMMENTS

  1. Hi,

    My problem with Importrange is that column A to M come from another Master Data spreadsheet in Google Sheets and column N to Z are manually added by me, only I can have access to the data from N to Z. If someone adds a row to the master data file, my data from N to Z gets bumped and it really needs to follow what’s in the same row.

    How do I get my cells to follow each other to make sure the data of Mister X follows what’s copied from importrange?

    Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.