INDIRECT

Returns a reference, given a text string.

Syntax:

INDIRECT(textref, type)

textref is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.The reference may then be used in formulas or functions requiring a reference - see the examples below.

type is an optional number; if 0, textref is taken to be in R1C1 notation, and otherwise (or if omitted) textref is taken to be in the usual A1 notation.

A named reference is recognised with either type.

Example:

INDIRECT("B2")

(in a cell) returns the contents of cell B2. INDIRECT("B2") returns a reference to cell B2, so this is the same as B2.

INDIRECT("B" & "2")

also returns the contents of cell B2. "B" & "2" becomes the text string "B2".

INDIRECT(D3)

where cell D3 contains the text B2 returns the contents of cell B2.

SUM(INDIRECT("A1:C3"))

returns the sum of the numbers in A1:C3.

INDIRECT("B2", 99)

returns the contents of cell B2. The type is non-zero, so B2 is evaluated as the usual A1 notation.

INDIRECT("R2C3", 0)

returns the contents of cell C2. The type is zero, so R2C3 is evaluated as R1C1 notation.

INDIRECT("R[3]C[1]", 0)

entered in cell B1, returns the contents of cell C4. The type is zero, so R[3]C[1] is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.

Application:

Dynamic Sales Summary

Imagine you have sales data for three months: January, February, and March. The data is organized in a table where each month's sales are listed. You want to create a summary table that automatically displays the total sales for a specific month, and you can change the month simply by typing its name in a cell.

Sales Data Table:


	A	B
1	Month	Sales ($)
2	January	$5,000.00
3	January	$6,500.00
4	January	$7,200.00
5	January	$4,800.00
6	January	$6,100.00
7	Month	Sales ($)
8	February	$8,000.00
9	February	$9,200.00
10	February	$7,500.00
11	February	$8,800.00
12	February	$9,500.00
13	Month	Sales ($)
14	March	$12,000.00
15	March	$15,000.00
16	March	$13,500.00
17	March	$14,200.00
18	March	$16,000.00

Summary Table:


	A	B
1	Month to View	Sales ($)
2	February	$4,300.00

How the INDIRECT function is used:

In cell B2, we want to display the total sales for the month entered in cell A2. The formula in B2 would be:

SUM(INDIRECT(A2 & "_Sales"))

Explanation:

A2: This cell contains the name of the month you want to view, which is "February" in this example.
A2 & "_Sales": This part of the formula concatenates the text from cell A2 with the text "_Sales". The result is the text string "February_Sales".
INDIRECT("February_Sales"): The INDIRECT function takes the text string "February_Sales" and converts it into a range reference. In this example, we have defined a Named Range for each month's sales data:
- January_Sales refers to the range B2:B6
- February_Sales refers to the range B8:B12
- March_Sales refers to the range B14:B18
SUM(...): The SUM function then calculates the sum of the values within the range referenced by INDIRECT. In this case, it sums the values in the "February_Sales" range (B8:B12), which results in 43000.

To make this work, you would first need to create the Named Ranges:

Select cells B2:B6 and in the Name Box (to the left of the formula bar), type January_Sales and press Enter.
Select cells B8:B12 and type February_Sales in the Name Box.
Select cells B14:B18 and type March_Sales in the Name Box.

Now, if you change the value in cell E2 to "March", the formula in F2 will dynamically update to SUM(INDIRECT("March_Sales")) and display the total sales for March, which is 69700.

This example demonstrates how INDIRECT enables a single, dynamic formula to reference different data ranges based on user input, without needing to change the formula itself.