Excel INDIRECT Function Using Sheet References

Excel Indirect function can be used to return a valid reference from a given text string. The cell reference provided in this formula will not change when you add or delete rows and/or columns. For Example, =INDIRECT(A1: E1) will always refer to the first 5 columns of the sheet even if new columns are added or deleted.

Key Takeaways:

• Dynamic Sheet Referencing: The INDIRECT function allows you to dynamically reference cells or ranges on different sheets by using text strings. For example, =INDIRECT("Sheet2!A1") refers to cell A1 on Sheet2.
• Flexible Cell References: You can create formulas that change their references based on user inputs. For instance, combining INDIRECT with a cell value (e.g., =INDIRECT(A1 & "!B2")) lets you reference cell B2 on the sheet name specified in A1.
• Cross-Sheet Data Management: By using INDIRECT, you can create a centralized formula that adapts to multiple sheets, making it easier to manage and retrieve data across a workbook without manually adjusting references.

 

Excel Indirect Function

This function does not perform any calculation or evaluate any logic or condition.

So, What does it do?

Excel Indirect function is used to indirectly reference a cell, or a range of cells of a sheet.

Formula breakdown:

=INDIRECT(ref_text, [a1])

What it means:

ref_text – return the referenced range

[a1] – specify the type of reference (A1 style or R1c1 style) mentioned in ref_text. Omit or type TRUE if the reference is an A1 style or enter FALSE if it is an R1C1 style.

The type of references are:

• A1 style – This is the most common reference that we use when we are trying to provide cell reference. In this, you first provide the column number (indicated by A, B, C, etc) and then the row number (indicated by 1, 2, 3, etc). Example, the highlighted cell in the image below is B3

 

• R1C1 style – This reference is opposite to the A1 style i.e. first the row number and then column number. For example, R3C2 refers to cell B3 which is row 3 and column 2 in a sheet.

 

---

The INDIRECT function is really cool as it can be used to reference sheet cells or a range of cells.

It opens up a lot of interesting possibilities as you can have fun creating flexible formulas, like indirectly summing the values that reside in another worksheet!

Let’s look at a basic example to understand how this function works!

 

Basic use of Indirect Function

In the example below, you have used the Indirect function to return a value when the referenced range is a cell.

You have the sales amount 24,640 stored in cell D2 and the text D2 stored in cell I2.

Now, when you type the formula =INDIRECT(I2) Excel will follow the steps below:

• It will first go to cell I2 which the formula refers to and get the value D2.
• Next, it will go to cell D2 and pick the value 24,640 and display that result.

As you can see, the Excel Indirect Function converts the text string D2 into a cell reference.

Using R1c1 style, the same formula will be: =INDIRECT(K2, FALSE)

Cell K2 contains the cell reference in the R1C1 style.

Make sure to provide the second argument of the function i.e. [a1]. Here, it is FALSE.

Until now, you have covered how to use Excel Indirect function when you are trying to fetch values from the same sheet.

Let’s see how it works when you want to use an Excel reference cell in another sheet dynamically!

Reference a Cell in Another Sheet

When the referenced range is a cell of a Sheet, the Excel INDIRECT function will go and return the content of the referenced cell in that Sheet.

Read below to learn how to insert Sheet reference in Excel Indirect Formula.

Watch it on YouTube and give it a thumbs-up!

Make sure to download the Excel Workbook below and follow along:

Download excel workbookIndirect-Sheet-Reference.xlsx

In this example, you have two Excel Indirect Sheet name – Summary Page & January.

In Sheet – January, you have sales data for the month of January and a total sales amount achieved in that month mentioned in cell I3.

In the sheet – Summary Page, you want to pull the total sale figure in cell E11.

You can use the formula below:

=INDIRECT(“JANUARY!I3”)

 

Sheet references are in the format of SHEETNAME!CELL, an example would be January!I3 which would mean the cell I3 in the worksheet named January.

Say we enter in cell C11 the worksheet name: January

In another cell, we enter =INDIRECT(C11&”!I3″), which will translate into =INDIRECT(“January!I3”) as the ampersand & will concatenate/join the two strings together.

NB: Highlighting the contents between the parenthesis and pressing the F9 key will confirm this:

 

This will indirectly return the value that resides in cell I3 in the worksheet named January, which has the total sales amount of $2,718,086 in our example below:

This is how Excel sum indirect another sheet reference works!

The same formula for R1C1 style will be: =INDIRECT(C17&”!R3C9″,FALSE)

 

Reference a Range of Cells in Another Sheet

When the referenced range is a range of cells of a Sheet, the Excel INDIRECT function will go and return the content of the referenced cells of that Sheet.

Instead of fetching the summation value from one cell, you can pull data from a range of cells i.e. D:D.

 

You can use the formula below:

=SUM(INDIRECT(“FEBRUARY!D:D”))

 

We can then enter a SUM function which will total the referenced cells of that sheet.

Say we enter in cell G12 the worksheet name: February

In another cell we enter =SUM(INDIRECT(G12&”!D:D”)), which will translate into =SUM(INDIRECT(“February!D:D”)) as the ampersand & will concatenate/join the two strings together.

This will indirectly Sum the values that reside in column D in the worksheet named February, which is $2,584,131 in the example below (click to expand image):

This is how Excel will use indirect function excel different sheet reference and fetch values from there!

The same formula for R1C1 style will be: =SUM(INDIRECT(G17&”!C4:C4″,FALSE))

Frequently Asked Questions

How do I reference a cell on another sheet using INDIRECT?

To reference a cell on another sheet, use the formula =INDIRECT("SheetName!A1"), replacing "SheetName" with the sheet’s name and A1 with the desired cell reference. For dynamic references, you can use a cell containing the sheet name, like =INDIRECT(A1 & "!B1"), where A1 contains the sheet name.

Can I use INDIRECT to reference ranges across multiple sheets?

Yes, you can reference ranges dynamically with INDIRECT. For example, to sum a range A1:A10 on a sheet specified in cell A1, use =SUM(INDIRECT(A1 & "!A1:A10")). This adjusts the formula based on the sheet name provided in cell A1.

What are the limitations of the INDIRECT function?

The INDIRECT function does not work with closed workbooks, as it requires the referenced workbook to be open. Additionally, it can make formulas harder to audit because the references are not directly visible, and it may slow down performance in large datasets due to its volatile nature (recalculating every time a change is made).