What is the INDIRECT Function
The INDIRECT function converts a text string into a cell reference. This lets you build dynamic references where the cell address itself comes from another cell. It is very useful for dynamic sheet references and flexible dashboards.
Formula: =INDIRECT(ref_text, [a1])
Practical Example 1
A2 contains the text B5. You want the value of cell B5.
Type: =INDIRECT(A2)
Result: the value stored in cell B5
Practical Example 2
Refer to a sheet name stored in a cell.
Type: =INDIRECT(A2 & !B5)
Result: the value of B5 on the sheet whose name is in A2
Practical Example 3
Build a dynamic SUM range.
Type: =SUM(INDIRECT(A2 & : & B2))
Result: the sum of the range defined by A2 and B2
Practical Example 4
Use INDIRECT for a dynamic named range.
Type: =SUM(INDIRECT(MyRange))
Result: the sum of the named range MyRange
When to Use It
INDIRECT is used for dynamic sheet references, for building flexible dashboards, for referencing named ranges dynamically, and for any formula where the cell address needs to come from another cell. Note that INDIRECT breaks when rows or columns are inserted or deleted.