Pinterest Pixel

Excel INDIRECT Function Using Sheet References

Excel Indirect function can be used to return a valid reference from a given text string. The... read more

Download Excel Workbook
Bryan
Posted on

Overview

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.

In this article, you will learn in details regarding the following:

 

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
See also  Excel Array Formulas Explained

Excel INDIRECT Function Using Sheet References

 

  • 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.Excel INDIRECT Function Using Sheet References

 


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.

Excel INDIRECT Function Using Sheet References

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.

Excel INDIRECT Function Using Sheet References

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.

Excel INDIRECT Function Using Sheet References

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

See also  TRANSPOSE Formula in Excel

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!

Excel INDIRECT Function Using Sheet References | MyExcelOnline

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

DOWNLOAD EXCEL WORKBOOK

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.

Excel INDIRECT Function Using Sheet References

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

Excel INDIRECT Function Using Sheet References

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.

See also  How to get Remainder in Excel using MOD Formula

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

Excel INDIRECT Function Using Sheet References

 

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:

Excel INDIRECT Function Using Sheet References

This is how Excel sum indirect another sheet reference works!

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

Excel INDIRECT Function Using Sheet References

 

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.

Excel INDIRECT Function Using Sheet References

 

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):

Excel INDIRECT Function Using Sheet References

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))

See also  INDEX Function Introduction

Excel INDIRECT Function Using Sheet References

 

Conclusion

In this article, you have learned about Indirect Function in Excel – Syntax, Basic Example, using cell reference A1 style & R1C1 style, when reference cell is in another sheet & when reference is a range of cells in another sheet.

There is a lot that this incredible function – INDIRECT can do. Click here to know all about it!

Further Learning:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!

HLOOKUP Function in Excel: Introduction

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Excel INDIRECT Function Using Sheet References | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!