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.