INDIRECT for dynamic references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this INDIRECT formula?

Given cell A1 contains the text B2 and cell B2 contains the number 150, what value will the formula =INDIRECT(A1) return?

A 150

B B2

C A1

D #REF! error

💡 Hint

INDIRECT uses the text in A1 as a cell reference.

The formula =INDIRECT(A1) reads the text in A1, which is B2, and returns the value in cell B2, which is 150.

❓ Function Choice

intermediate

Which formula correctly uses INDIRECT to sum a range defined in cells?

Cells A1 and A2 contain the text C1 and C5 respectively. Which formula sums the range from C1 to C5 using INDIRECT?

A =SUM(INDIRECT(A1 & ":" & A2))

B =SUM(INDIRECT(A1, A2))

C =SUM(INDIRECT(A1:A2))

D =SUM(INDIRECT("A1:A2"))

💡 Hint

Concatenate the start and end cell references with a colon inside INDIRECT.

Option A concatenates the text in A1 and A2 with a colon to form a valid range reference like C1:C5. INDIRECT then converts this text to a range for SUM.

🎯 Scenario

advanced

Using INDIRECT to reference different sheets dynamically

You have two sheets named January and February. Cell A1 on the current sheet contains the text January. Cell B2 in both sheets contains sales data. Which formula correctly retrieves the sales data from the sheet named in A1?

A =INDIRECT("A1!B2")

B =INDIRECT("'" & A1 & "'!B2")

C =INDIRECT(B2 & "!" & A1)

D =INDIRECT(A1 & "!B2")

💡 Hint

Sheet names with spaces or special characters need single quotes around them.

Option B correctly adds single quotes around the sheet name from A1 and appends !B2 to reference cell B2 on that sheet. This works even if sheet names have spaces.

📊 Formula Result

advanced

What error does this INDIRECT formula produce?

If cell A1 contains the text SheetX!Z100 but SheetX does not exist, what will the formula =INDIRECT(A1) return?

A #VALUE! error

B 0

C #REF! error

D Empty cell

💡 Hint

INDIRECT returns an error if the reference does not exist.

Since SheetX does not exist, INDIRECT cannot resolve the reference and returns a #REF! error.

❓ data_analysis

expert

How many cells are summed by this formula?

Cells A1 and A2 contain the text D3 and D7 respectively. What is the number of cells summed by the formula =SUM(INDIRECT(A1 & ":" & A2))?

A 7

B 4

C 6

D 5

💡 Hint

Count all cells from D3 to D7 inclusive.

The range from D3 to D7 includes cells D3, D4, D5, D6, and D7, which is 5 cells total.