Cross-sheet references in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the value from cell A1 in the sheet named 'Data'.

Google Sheets

=Data![1]

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

AB1

BA1

C1A

DSheet1!A1

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference like B1 instead of A1.

Including the sheet name twice like 'Sheet1!A1'.

Swapping the row and column like '1A'.

The correct way to reference cell A1 in another sheet named 'Data' is Data!A1.

2fill in blank

medium

Complete the formula to sum the range B2:B5 from the sheet named 'Sales'.

Google Sheets

=SUM(Sales![1])

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

AA2:A5

BB5:B2

CB2:B5

DSales!B2:B5

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the range like B5:B2 which is invalid.

Using a different column like A2:A5.

Including the sheet name twice like Sales!Sales!B2:B5.

To sum cells B2 to B5 in the 'Sales' sheet, use Sales!B2:B5 inside the SUM function.

3fill in blank

hard

Fix the error in the formula to reference cell C3 from the sheet named '2023 Data'.

Google Sheets

='[1]'!C3

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

A2023 Data

B2023Data

C2023-Data

D'2023 Data'

Attempts:

3 left

💡 Hint

Common Mistakes

Adding extra quotes inside the blank causing double quotes.

Removing the quotes entirely which causes errors with spaces.

Using sheet names without spaces incorrectly.

When a sheet name has spaces, you must enclose it in single quotes in the formula. The formula already has quotes around the blank, so just put the sheet name without extra quotes.

4fill in blank

hard

Fill both blanks to create a formula that gets the value from cell D4 in the sheet named 'Summary 2024'.

Google Sheets

='[1]'![2]

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

ASummary 2024

BSummary2024

CD4

D4D

Attempts:

3 left

💡 Hint

Common Mistakes

Using cell reference with row before column like 4D.

Not quoting the sheet name with spaces.

Using sheet name without spaces incorrectly.

The sheet name has a space, so it must be in single quotes. The cell reference is D4.

5fill in blank

hard

Fill all three blanks to create a formula that sums the range A1:A10 from the sheet named 'Expenses 2023'.

Google Sheets

=SUM('[1]'![2]:[3])

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

AExpenses 2023

BA1

CA10

DB10

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong cell references like B10.

Not quoting the sheet name with spaces.

Swapping the start and end of the range.

The sheet name with spaces needs quotes. The range is from A1 to A10.