Indirect Range in Google Sheets (3 Easy Ways)

Today, we will look at how to pull data from an indirect range in Google Sheets. Just a disclaimer, this article focuses on cell ranges, not values of individual cells.

Let’s get started.

3 Ways to Use Indirect Range in Google Sheets

The core of our article today focuses on the INDIRECT function. While we may not dive into the function here, you can visit our Dynamic Cell Reference in Google Sheets article for a better breakdown of its capabilities with examples.

1. Locking Cell References for Indirect Range

Building on the idea of indirectly referencing cells in Google Sheets, we have our first practical use of using INDIRECT to reference range or ranges, that is to lock the starting and ending cell references of the range of cells we are going to use.

To show this example, we have the following worksheet:

main worksheet for indirect range in google sheets

Here, we have the costs incurred by certain expenses in each month. We have used the first four months of the year.

You will also notice the Range Start and Range End cells below the table. Here is where we will be designating the range of cells that we are going to calculate, and in this case, using the SUM function.

Let’s say we want to calculate the total expenses for the first three months of the year. We can simply do this by using the SUM function and the range of cells like so:

using the basic sum function

But, for the sake of extra security, we can designate the start and the end cells of the range separately in a different cell. Our formula will then incorporate the INDIRECT function to indirectly reference the new cell range.


using indirect function to pull an indirect range in google sheets

Formula Breakdown:

  • INDIRECT(C8&”:”&C9): The indirect range reference away from the table. Helps to protect the formula cell from any changes.
    • C8: References the starting cell of the range.
    • &”:”&: The colon (:) defines the range in Google Sheets. It is concatenated by the ampersand (&) on both sides with the cell reference.
    • C9: References the ending cell of the range.
  • SUM: Sums the range indirectly defined by INDIRECT.

This type of formula works best when you have your formula cells secured but still want to make minor changes, especially to cell references indirectly.

Read More: Lock Cell Reference in Google Sheets (3 Ways)

2. Indirect Range from a Different Worksheet

Indirect range references are not confined to one worksheet. They can be retrieved from different worksheets in your spreadsheet.

We continue from the last example of summing expenses of each month. This time, each month has its respective worksheet.

multiple worksheets for the same spreadsheet

Our main worksheet, Expenses, now looks like this:

new look for the Expenses worksheet

Note: You can apply the drop-down list in cell C2 by simply navigating to Data Validation from the Data tab (Data > Data Validation) and applying the following conditions:

conditions for the drop-down list in data validation

We already know that to reference a range from another sheet we have to use the following format:


In our case, it will be:


Now, using INDIRECT, our cell references will be:

  • C2: Month Name
  • C3: Range Start
  • C4: Range End

Thus, our formula for the Total Expenses:


indirect range in google sheets from another worksheet

And thanks to the drop-down month list, we can calculate the expenses of each month.

indirect range in google sheets from another worksheet animated

With this method, not only is your formula cell protected, but you are also free to work with multiple sheets of your spreadsheet.

Read More: Reference Another Sheet in Google Sheets (4 Easy Ways)

Similar Readings

3. Using Named Ranges to Indirectly Reference Cells

Another aspect of Google Sheets that the INDIRECT function takes advantage of is Named Ranges.

To apply a named range, you simply select the range of cells, navigate to Data > Named ranges option.

navigating to named ranges from the data tab

Next, you simply apply your desired name to the selected range and click Done. We have applied a Named Range for the Expenses column for each month.

all the named ranges in our spreadsheet

The fun part about named ranges is that you can pull this range from any worksheet. And taking advantage of that fact, we will apply it to our Expenses worksheet.


indirect range in google sheets using named range

However, a limitation of this formula is the named range itself. Each named range is different. So, if we change the month name in the Expenses worksheet, we will get an error in value, as the named range given does not change.

limitation with named ranges

The Total Expenses shown is still $1950 (January) whereas it was supposed to be $2100 for March.

total expenses of march

To remedy this problem, we must make some modifications to our formula. That is, making sure the month name also changes for the named range from the drop-down menu. Luckily for us, the named ranges were named according to their respective months, but also leaving the general naming convention intact for all of them.


modified formula for indirect range in google sheets using named range

The new formula in action:

using named range to indirectly reference cell range animated

Note: Not only Named Ranges, but you can also use INDIRECT to indirectly refer to any range that is in the form of a string. For example, a cell may contain the string “C3:C5” and you can use INDIRECT to utilize this range. You can learn more about it in our Cell Reference From String in Google Sheets article.

Final Words

That concludes all the ways we can apply indirect range in Google Sheets from the same or different worksheets, or a different spreadsheet entirely. We hope that the three major methods we have discussed come in handy. Know that these are general examples, you can modify these methods and ideas to suit your needs.

Please feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply