Excel In Your Office: Functions: INDIRECT

INDIRECT function returns the reference to a cell based on its string representation. It returns the reference specified by a text string. References are immediately evaluated to display their contents. Function syntax is as below.

INDIRECT(ref_text, [A1])

ref_text is a textual representation of a cell reference.

[A1] is optional. It is either a TRUE or FALSE value. TRUE (or if this argument is omitted) indicates that ref_text will be interpreted as an A1-style reference. FALSE indicates that ref_text will be interpreted as an R1C1-style reference. (We will see more on this referencing style in one of the future posts)

Example 1

Let’s see the actual usage of this function. INDIRECT function allows you to input the address of one cell in another and get data from the first cell by referencing the second. Let’s assume this: cell A1 has a value C3. Cell C3 has a value 10. Now, entering =INDIRECT(A1) in any cell fetches 10. So, cell A1 value which is C3 is evaluated to get its value.

Example 2

Taking it further, assume this situation. I have a file where I compile 12 months data, each month in a different sheet. Each sheet is named by the month; January, February etc. I have a summary sheet that displays key calculations for a particular month. I have a drop-down list in the summary sheet, when I select a month from the drop-down list, few key numbers from that month needs to be populated. For example, one of key number to be populated includes a formula to give sum of respective month's sheet cells F1:F10 i.e. like =SUM(January!F1:F10) for January month. Is there any way that I can do this without manually writing so many formulas?

As usual, Excel says yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:

=SUM(INDIRECT(B1&"!F1:F10"))

Whatever month is selected in B1, this function returns that particular month related data by summing cells F1 to F10 of that month named sheet.

Example 3

You need to sum a series of cells in column A but starting row and ending row numbers are dynamic (changes frequently). If you write a SUM formula, you will have to update it each time your start and end row number changes. You can solve this problem using INDIRECT function. Suppose you entered your starting row in cell B1 and ending row in cell C1. Then, you can use the formula;

=SUM(INDIRECT("A"&B1&":A"&C1))

If B1 contains 5 and C1 contains 10, this evaluates to the string "A5:A10". INDIRECT function converts this string to an actual range reference, which is passed to the SUM function.

Example 4

In our post relating to relative & absolute references, we saw this example. (Refer to section: Point to remember)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax: