INDIRECT for dynamic references in Excel - Step-by-Step Guide

Introduction

INDIRECT lets you create a cell reference from text. It helps when you want formulas to change which cells they look at based on other cell values.

When you want to change the cell a formula uses by typing a cell address in another cell.

When you have multiple sheets and want to pick which sheet to get data from by typing the sheet name.

When you want to build a formula that adjusts automatically if you change a part of the reference.

When you want to avoid rewriting formulas for different ranges by using a cell to hold the range address.

When you want to combine text and numbers to create a reference inside a formula.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula starts appearing in the cell

💡 Start with =INDIRECT(

Step 3: Type

- inside the parentheses of INDIRECT

You enter a text string that looks like a cell reference, for example, "A1"

💡 Use quotes around the cell reference text, like "A1"

Step 4: Close

- the parentheses and press Enter

The cell shows the value from the referenced cell, for example, the value in A1

Step 5: Change

- the text inside the INDIRECT formula or the cell holding the reference text

The formula updates to show the value from the new referenced cell

Before vs After

Before

Cell B1 contains the text "A1" and cell A1 contains the number 10. Cell C1 is empty.

After

Cell C1 contains the formula =INDIRECT(B1) and shows the value 10 from cell A1.

Settings Reference

ref_text

📍 inside the INDIRECT formula parentheses

The text string that INDIRECT converts into a cell reference

Default: none

a1

📍 second argument of INDIRECT formula

Determines if the reference is in A1 style (TRUE) or R1C1 style (FALSE)

Default: TRUE

Common Mistakes

Typing =INDIRECT(A1) without quotes when A1 does not contain a valid reference text.

INDIRECT expects a text string or a cell containing text that looks like a reference. Without quotes or valid text, it causes an error.

Put the reference as text in quotes like =INDIRECT("A1") or ensure A1 contains the text "A1".

Using INDIRECT with a reference to a closed workbook.

INDIRECT cannot get data from closed workbooks and will return an error.

Make sure the referenced workbook is open when using INDIRECT.

Summary

INDIRECT turns text into a cell reference so formulas can change which cells they use.

Use INDIRECT when you want formulas to be flexible and depend on text input for references.

Remember to use quotes around text references or refer to cells that contain valid reference text.