INDIRECT for dynamic references in Google Sheets - Step-by-Step Guide

Introduction

INDIRECT helps you use text to create a cell or range reference. This means you can change which cell or range a formula looks at by changing text in another cell. It solves the problem of needing formulas that update automatically when you change a reference.

When you want a formula to refer to different sheets based on a cell value.

When you want to change the column or row a formula uses without rewriting the formula.

When you want to build a reference from parts typed in separate cells.

When you want to create a dynamic range for charts or calculations.

When you want to avoid manually updating formulas after moving or copying data.

Steps

Step 1: Click

- a cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula is entered but not yet calculated

💡 Start with =INDIRECT(

Step 3: Type

- inside the parentheses

You enter a text string or a cell reference that contains text representing a cell or range

💡 For example, type "A1" or use a cell reference like B1 if B1 contains text 'A1'

Step 4: Close

- the parentheses and press Enter

The formula shows the value from the cell or range referenced by the text

Step 5: Change

- the text in the referenced cell (if used)

The INDIRECT formula updates to show the value from the new reference

Before vs After

Before

Cell B1 contains text 'A1'. Cell C1 has formula =INDIRECT("A1") showing value 10.

After

Change B1 text to 'B2'. Cell C1 formula =INDIRECT(B1) now shows value from cell B2.

Settings Reference

Reference text

📍 Inside INDIRECT formula parentheses

Defines which cell or range INDIRECT will refer to

Default: No default, must provide reference text

A1 notation or R1C1 notation

📍 Second optional argument of INDIRECT formula

Specifies the style of the reference text

Default: TRUE (A1 style)

Common Mistakes

Typing =INDIRECT(A1) when A1 does not contain text but a number.

INDIRECT expects a text string that looks like a cell reference. A number will cause an error.

Make sure the referenced cell contains text like 'A1' or use quotes around the reference.

Using INDIRECT with a reference to a sheet name without proper quotes.

Sheet names with spaces or special characters need single quotes around them in the reference text.

Write the reference as "'Sheet Name'!A1" inside INDIRECT.

Summary

INDIRECT lets you build cell or range references from text, making formulas dynamic.

You can change the reference by changing the text, without editing the formula itself.

Remember to use text strings for references and handle sheet names carefully.