INDIRECT for dynamic references in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INDIRECT function do in Google Sheets?

The INDIRECT function takes a text string and turns it into a cell reference. It lets you create dynamic references that change based on the text you provide.

beginner

How would you use INDIRECT to refer to cell A1 on a sheet named in cell B1?

Use =INDIRECT(B1 & "!A1") to create a reference to cell A1 on the sheet named in cell B1.

intermediate

Why is INDIRECT useful when you want to change references without editing formulas?

Because INDIRECT uses text strings for references, you can change the text (like sheet names or cell addresses) and the formula updates automatically without rewriting it.

beginner

What happens if the text inside INDIRECT does not form a valid reference?

Google Sheets will show a #REF! error because INDIRECT cannot find the cell or range described by the text.

intermediate

Can INDIRECT be used to reference ranges dynamically? Give an example.

Yes. For example, =SUM(INDIRECT("A" & 1 & ":A" & 5)) sums cells A1 to A5. The range is built from text parts combined dynamically.

What does INDIRECT("B2") return in Google Sheets?

A The text "B2"

B The value in cell B2

C An error

D The formula in B2

INDIRECT("B2") returns the value in cell B2 by converting the text "B2" into a reference.

How can INDIRECT help when you want to sum a range on a sheet named in cell A1?

A =SUM(INDIRECT(A1 & "!A1:A10"))

B =SUM(A1!A1:A10)

C =SUM("A1!A1:A10")

D =SUM(INDIRECT("A1"))

Option A builds a dynamic reference to the range A1:A10 on the sheet named in cell A1.

What error appears if INDIRECT references a non-existent cell?

A #DIV/0!

B #VALUE!

C #NAME?

D #REF!

INDIRECT shows #REF! error when the reference text does not point to a valid cell or range.

Which of these is a correct use of INDIRECT to reference cell C3?

A =INDIRECT("3C")

B =INDIRECT(C3)

C =INDIRECT("C3")

D =INDIRECT(C"3")

INDIRECT requires a text string like "C3" to create a reference.

Why might you use INDIRECT instead of a direct cell reference?

A To make formulas update when sheet names or ranges change

B To speed up calculations

C To prevent errors

D To format cells

INDIRECT allows formulas to update dynamically when the referenced text changes, like sheet names or ranges.

Explain how INDIRECT can be used to create a dynamic reference to a cell or range in another sheet.

Think about how text strings can build a reference.

Describe a situation where using INDIRECT would make your spreadsheet easier to update.

Imagine you rename sheets often or want to sum different ranges without changing formulas.