Normally locking cell reference is not possible with Google Sheets Importrange function. But there are two workarounds. You can freeze cell in Importrange in Google Sheets either by using a Query or Named Ranges. Both aspects, I’m going to elaborate in this Google Sheets tutorial.
Lock Cell Reference in Google Sheets Importrange – Why It’s Required?
Locking Cell Reference or you can say, Freezing a Cell in Importrange is a must when we want to import only a specific cell value normally a cell containing total.
In this screenshot you can see that cell H13 contains the total of H2:H12. I can easily import this cell in another worksheet with the following generic IMPORTRANGE formula.
But the problem here is, when I delete or insert any row between H2:H12, the cell containing total in H13 moves. When you delete 1 row, it became H12. But our Importrange formula keeps showing the value in Cell H13 and which is obviously wrong!
How to Freeze Cell in Importrange in Google Sheets
There are two options that you can consider to freeze a cell in Importrange in Google Sheets. You can lock or freeze a cell in Importrange either by using a QUERY function or with Named Ranges. Let’s go to that.
Lock or Freeze Cell in Importrange Using Named Ranges
Look at the above screenshot where our total is in Cell H13. It’s the sum of the range H2:H12.
First select this range H2:H12. Then go to the menu Data > Named Ranges.
Name the Range “Total”. The range is already using our selected range H2:H12 with sheets name. Here my sheets name is “OFFSET Dyanmic!”. So you just need to name the range and click done.
Now open the sheet where you want to import the value of cell H13. Apply the Importrange formula as below. It’s a generic formula. You should change the spreadsheet_url with real URL.
As said above, you just need to replace the spreadsheet_URL with real URL of your sheet containing the data set above. In the above formula “Total” is the named ranges.
Pros and Cons of Locking Cell in Importrange in Google Sheets Using Named Ranges
You can delete or insert any row between the range H2:H13. The named range will automatically get adjusted and so you will get the correct cell value imported.
When you insert any row before H2 or after H13, that won’t reflect in our above formula result. In other words, if you change the SUM formula range in H13, it won’t reflect in our above formula.
The below formula can handle this disadvantage of Named Ranges in Importrange.
Lock or Freeze Cell in Importrange Using Query
Here is the second option to Freeze Cell in Importrange in Google Sheets. This is the recommended option and most dynamic. To make the below formula work, there is an additional requirement. I’ll come to that.
Here in this example also, I am going to import the cell content in H13, that is the SUM of H2:H12 in another sheet. But see the text in D13 below. Yes, it’s required.
You should place a text “Total” in the row 13 in any column containing text value. Here the suggested cells are A13, B13, D13, or E13 as these columns contain text values. So here I opted D13. This’s an additional requirement to freeze cells using Query.
Now see the generic formula. You should replace the spreadsheet_url with your original URL.
=query((importrange(“spreadsheet_URL”,”OFFSET Dyanmic!A1:H”)),”Select Col8 where Col4=’Total'”,0)
Don’t get confused. Here “OFFSET Dyanmic!A1:H” is the data range that means OFFSET Dyanmic! is my sheet name. You should change that to your sheet name.
The above is the best formula to Freeze Cell in Importrange in Google Sheets. Why? Just take a look at the Pros and Cons of this formula.
Similar: Offset Function Examples in Google Sheets and Dynamic Ranges [A Must to Follow Tutorial for All Spreadsheet Lovers]
No limitation, you can add or delete any rows. It won’t affect the locked cell.
See the cell D13. A text saying “Total”, “Sub Total”, “Grand Total” or any such meaningful word is required in the row where the cell to freeze resides. If you want to lock Cell D50, in the same row in any other cell you should type any meaningful text to use in Query where Clause to filter. That’s all.
This way you can Freeze Cell in Importrange in Google Sheets.