INDIRECT for dynamic references in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the value from cell A1 using INDIRECT.

Google Sheets

=INDIRECT([1])

Drag options to blanks, or click blank then click option'

A"A1"

B"1A"

CA1

DINDIRECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using A1 without quotes causes an error.

Putting the cell reference in wrong order like "1A".

The INDIRECT function needs a text string representing the cell reference. So, "A1" is correct.

2fill in blank

medium

Complete the formula to get the value from the cell in column B and row number in cell A1.

Google Sheets

=INDIRECT("B" & [1])

Drag options to blanks, or click blank then click option'

AB1

BA1

C"A1"

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Putting "A1" in quotes treats it as text, not the cell value.

Using a fixed number like 1 instead of the dynamic row.

We want to use the row number from cell A1, so we use A1 without quotes to get its value.

3fill in blank

hard

Fix the error in the formula to reference the sheet named in cell A1 and cell B2.

Google Sheets

=INDIRECT([1] & "!B2")

Drag options to blanks, or click blank then click option'

AINDIRECT(A1)

B"A1"

CA1

DA1 & "!"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting "A1" in quotes treats it as text, not the sheet name.

Using INDIRECT inside INDIRECT causes errors.

We want the sheet name from cell A1, so use A1 without quotes to get its value.

4fill in blank

hard

Fill both blanks to create a dynamic reference to the cell in the sheet named in A1, column in B1, and row 5.

Google Sheets

=INDIRECT([1] & [2] & B1 & "5")

Drag options to blanks, or click blank then click option'

AA1

B"!"

CB1

D"B"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting the "!" between sheet name and cell reference.

Using quotes around cell references incorrectly.

We get the sheet name from A1, add "!" to separate sheet and cell, then add the column from B1 and row 5.

5fill in blank

hard

Fill all three blanks to create a dynamic reference to the cell in the sheet named in A1, column letter in B1, and row number in C1.

Google Sheets

=INDIRECT([1] & [2] & [3])

Drag options to blanks, or click blank then click option'

AA1

B"!"

CB1 & C1

DB1

Attempts:

3 left

💡 Hint

Common Mistakes

Not combining column and row correctly in the last blank.

Missing the "!" separator between sheet and cell.

We use A1 for sheet name, add "!" separator, then combine column letter in B1 and row number in C1 for the cell reference.

Practice

1. What does the INDIRECT function do in Google Sheets?

easy

A. It finds the maximum value in a range.

B. It sums all numbers in a range.

C. It converts a text string into a cell or range reference.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of INDIRECT

   The INDIRECT function takes a text string and treats it as a reference to a cell or range.

2. Step 2: Compare with other options

   Options B, C, and D describe other functions like SUM, MAX, and SORT, not INDIRECT.

3. Final Answer:

   It converts a text string into a cell or range reference. -> Option C

4. Quick Check:

   INDIRECT = converts text to reference [OK]

Hint: Remember: INDIRECT turns text into a live cell reference [OK]

Common Mistakes:

• Confusing INDIRECT with SUM or MAX functions
• Thinking INDIRECT changes cell values
• Assuming INDIRECT sorts data

2. Which of these is the correct syntax for the INDIRECT function in Google Sheets?

easy

A. INDIRECT(reference_text, [a1])

B. INDIRECT(reference_text; [a1])

C. INDIRECT(reference_text a1)

D. INDIRECT(reference_text, a1, extra)

Solution

1. Step 1: Recall the syntax format

   The correct syntax uses a comma to separate arguments: INDIRECT(reference_text, [a1]) where a1 is optional.

2. Step 2: Check other options for errors

   INDIRECT(reference_text; [a1]) uses a semicolon which is incorrect in Google Sheets formulas. INDIRECT(reference_text a1) misses commas and brackets. INDIRECT(reference_text, a1, extra) has an extra argument which is invalid.

3. Final Answer:

   INDIRECT(reference_text, [a1]) -> Option A

4. Quick Check:

   Correct syntax uses comma and optional second argument [OK]

Hint: Use comma between arguments; second argument is optional [OK]

Common Mistakes:

• Using semicolons instead of commas
• Adding extra arguments
• Omitting commas between arguments

3. Given cell A1 contains the text "B2" and cell B2 contains the number 50, what will the formula =INDIRECT(A1) return?

medium

A. The text "B2"

B. The number 50

C. An error because B2 is text

D. Blank cell

Solution

1. Step 1: Understand the INDIRECT reference

   The formula INDIRECT(A1) reads the text in A1, which is "B2", and treats it as a reference to cell B2.

2. Step 2: Check the value in referenced cell

   Cell B2 contains the number 50, so INDIRECT(A1) returns 50.

3. Final Answer:

   The number 50 -> Option B

4. Quick Check:

   INDIRECT("B2") = value in B2 = 50 [OK]

Hint: INDIRECT uses text as address, returns that cell's value [OK]

Common Mistakes:

• Thinking INDIRECT returns the text itself
• Expecting an error if referenced cell has a number
• Confusing cell content with reference

4. You wrote the formula =INDIRECT(A1:B1) but it returns an error. What is the likely problem?

medium

A. INDIRECT only works with numbers, not text.

B. You forgot to add quotes around A1:B1.

C. The formula needs a third argument to work.

D. INDIRECT cannot take a range as reference_text, only a single cell text.

Solution

1. Step 1: Analyze the argument type

   The INDIRECT function expects a single text string as reference_text, not a range like A1:B1.

2. Step 2: Understand why the error occurs

   Passing a range A1:B1 as reference_text causes an error because INDIRECT cannot process multiple cells as text input.

3. Final Answer:

   INDIRECT cannot take a range as reference_text, only a single cell text. -> Option D

4. Quick Check:

   INDIRECT needs single text reference, not range [OK]

Hint: INDIRECT needs one text cell, not multiple cells [OK]

Common Mistakes:

• Passing a range instead of a single cell text
• Adding unnecessary extra arguments
• Thinking INDIRECT works only with numbers

5. You want to sum values in different sheets dynamically. Cell A1 contains the sheet name (e.g., "Sales2023"). Which formula correctly sums range B1:B10 in the sheet named in A1?

hard

A. =SUM(INDIRECT(A1 & "!B1:B10"))

B. =SUM(INDIRECT("A1!B1:B10"))

C. =SUM(INDIRECT(B1:B10 & "!" & A1))

D. =SUM(INDIRECT("B1:B10"))

Solution

1. Step 1: Build the dynamic reference string

   Concatenate the sheet name in A1 with "!B1:B10" to create a text like "Sales2023!B1:B10".

2. Step 2: Use INDIRECT to convert text to reference

   INDIRECT(A1 & "!B1:B10") converts the text to a range reference on the specified sheet.

3. Step 3: Sum the values in that range

   SUM(...) adds all numbers in the referenced range.

4. Final Answer:

   =SUM(INDIRECT(A1 & "!B1:B10")) -> Option A

5. Quick Check:

   Concatenate sheet name + range, then INDIRECT + SUM [OK]

Hint: Join sheet name + "!range" inside INDIRECT for dynamic refs [OK]

Common Mistakes:

• Putting cell references inside quotes incorrectly
• Swapping sheet name and range order
• Not concatenating sheet name and range properly