INDIRECT for dynamic references in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This sheet has cells with text references (A1, B1, D1, E1) and numeric values in B2 and C3. The text in A1 and B1 are cell addresses. D1 and E1 contain references to those address cells.

Cell	Value
A1	B2
B1	C3
B2	10
C3	20
D1	A1
E1	B1

Formula Trace

=INDIRECT(A1) + INDIRECT(B1)

Step 1: INDIRECT(A1)

Step 2: INDIRECT(B1)

Step 3: INDIRECT(A1) + INDIRECT(B1)

Cell Reference Map

   A    B    C    D    E
1  B2   C3         A1   B1
2       10
3            20

The formula uses A1 and B1 as text references to other cells B2 and C3. The INDIRECT function converts these text references to actual cell references.

Result

   A    B    C    D    E
1  B2   C3         A1   B1
2       10
3            20
4  30

The formula result 30 is shown in cell A4 (or wherever the formula is placed). It is the sum of values in B2 and C3, accessed dynamically using INDIRECT.

Sheet Trace Quiz - 3 Questions

What value does INDIRECT(A1) return?

A 10

B B2

C 20

D C3

Key Result

INDIRECT converts a text string into a cell reference to access its value dynamically.