INDIRECT for dynamic references in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Excel

=INDIRECT([1])

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

A"1A"

BA1

C"A1"

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.

Excel

=INDIRECT("B" & [1])

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

AA1

B1

C"A1"

DB1

Attempts:

3 left

💡 Hint

Common Mistakes

Using "A1" as text instead of the value in A1.

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

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

3fill in blank

hard

Fix the error in the formula to reference cell C5 dynamically using INDIRECT.

Excel

=INDIRECT([1])

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

AC5

B"C5"

C"C" & 5

DINDIRECT("C5")

Attempts:

3 left

💡 Hint

Common Mistakes

Using C5 without quotes causes an error.

Using INDIRECT inside INDIRECT is unnecessary here.

The INDIRECT function requires a text string like "C5" to reference that cell.

4fill in blank

hard

Fill both blanks to create a formula that gets the value from the sheet named in cell A1 and cell B2.

Excel

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

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

AA1

B"A1"

C"B2"

DB2

Attempts:

3 left

💡 Hint

Common Mistakes

Putting A1 in quotes makes it a text, not a reference.

Using B2 without quotes causes an error.

Use A1 without quotes to get the sheet name, and "B2" as text for the cell reference.

5fill in blank

hard

Fill all three blanks to create a formula that sums the range from cell A1 to the cell in column B and row number in cell A2 on the sheet named in cell A3.

Excel

=SUM(INDIRECT([1] & "!A1:" & [2] & [3]))

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

AA3

B"B"

CA2

D"A3"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting A3 in quotes makes it text, not a reference.

Using B without quotes causes an error.

Putting A2 in quotes treats it as text, not a number.

Use A3 without quotes to get the sheet name, "B" as text for the column, and A2 without quotes for the row number.