INDIRECT for dynamic references in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INDIRECT function do in Excel?

The INDIRECT function returns the value of a cell or range based on a text string that represents a cell reference. It lets you create dynamic references that change when the text changes.

beginner

How would you use INDIRECT to refer to cell A1 dynamically if the cell address is in B1?

Use =INDIRECT(B1). If B1 contains "A1", INDIRECT returns the value in cell A1.

intermediate

Why is INDIRECT useful when working with multiple sheets?

INDIRECT can build sheet names dynamically from text, so you can refer to cells on different sheets without changing the formula manually.

beginner

What happens if the text string passed to INDIRECT is invalid?

Excel shows a #REF! error because INDIRECT cannot find a valid reference from the text string.

intermediate

Can INDIRECT be used to refer to ranges? Give an example.

Yes. For example, =SUM(INDIRECT("A1:A5")) sums the values in cells A1 through A5 dynamically.

What does =INDIRECT("B2") return?

A An error

B The text "B2"

C The address of cell B2

D The value in cell B2

INDIRECT("B2") returns the value stored in cell B2.

If cell A1 contains "C3", what does =INDIRECT(A1) return?

A The value in cell A1

B The value in cell C3

C The text "C3"

D An error

INDIRECT uses the text in A1 as a reference, so it returns the value in cell C3.

Which of these is a correct use of INDIRECT to sum cells A1 to A5?

A =INDIRECT(A1:A5)

B =INDIRECT(SUM(A1:A5))

C =SUM(INDIRECT("A1:A5"))

D =SUM(A1:A5)

SUM(INDIRECT("A1:A5")) sums the range A1:A5 using INDIRECT to create the reference.

What error appears if INDIRECT refers to a non-existent cell?

A #REF!

B #VALUE!

C #NAME?

D #DIV/0!

INDIRECT returns #REF! error if the reference text is invalid or points to a non-existent cell.

How can INDIRECT help when referencing different sheets dynamically?

A By using text strings to build sheet references

B By typing sheet names manually

C By copying formulas between sheets

D By locking cell references

INDIRECT can build sheet references from text strings, allowing dynamic sheet referencing.

Explain how INDIRECT can be used to create a dynamic cell reference in Excel.

Think about how text can tell Excel which cell to look at.

Describe a real-life scenario where INDIRECT helps manage data across multiple sheets.

Imagine monthly reports on separate sheets.