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.

Practice

1. What is the correct way to reference cell B2 from a sheet named Sales in another sheet?

easy

A. =Sales!B2

B. =B2!Sales

C. =Sales:B2

D. =Sheet2.B2

Solution

1. Step 1: Understand cross-sheet reference format

   In Google Sheets, to reference another sheet's cell, use SheetName!Cell.

2. Step 2: Apply the format to the example

   For sheet named Sales and cell B2, the reference is =Sales!B2.

3. Final Answer:

   =Sales!B2 -> Option A

4. Quick Check:

   SheetName!Cell = =Sales!B2 [OK]

Hint: Use SheetName!Cell to link sheets quickly [OK]

Common Mistakes:

• Swapping sheet name and cell reference order
• Using colon instead of exclamation mark
• Adding extra dots or symbols

2. Which of these formulas correctly references cell A1 from a sheet named 2023 Data?

easy

A. ='2023 Data'!A1

B. =2023 Data!A1

C. =2023_Data!A1

D. =2023Data!A1

Solution

1. Step 1: Recognize sheet names with spaces need quotes

   Sheet names with spaces must be enclosed in single quotes when referenced.

2. Step 2: Apply quotes to the sheet name

   The correct reference is ='2023 Data'!A1.

3. Final Answer:

   ='2023 Data'!A1 -> Option A

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Put single quotes around sheet names with spaces [OK]

Common Mistakes:

• Omitting quotes for sheet names with spaces
• Using underscores instead of spaces without quotes
• Using invalid characters in sheet names

3. Given the formula =SUM('Expenses 2024'!C2:C5) in Sheet1, what does it calculate?

medium

A. Sum of cells C2 to C5 in the current sheet

B. Sum of cells C2 to C5 in the sheet named Expenses2024 (no space)

C. Sum of cells C2 to C5 in all sheets

D. Sum of cells C2 to C5 in the sheet named 'Expenses 2024'

Solution

1. Step 1: Identify the sheet name in the formula

   The formula references the sheet named Expenses 2024 with quotes because of the space.

2. Step 2: Understand the range and function

   It sums cells from C2 to C5 in that specific sheet.

3. Final Answer:

   Sum of cells C2 to C5 in the sheet named 'Expenses 2024' -> Option D

4. Quick Check:

   SUM('Sheet Name'!Range) sums that sheet's range [OK]

Hint: Quotes mean exact sheet name with spaces [OK]

Common Mistakes:

• Assuming sum is from current sheet
• Ignoring quotes and misreading sheet name
• Thinking it sums multiple sheets

4. You wrote the formula =Sales!B2 in Sheet2, but it shows #REF!. What is the likely cause?

medium

A. You used a wrong cell reference format

B. Cell B2 is empty in the Sales sheet

C. The sheet named 'Sales' does not exist

D. You forgot to add quotes around Sales

Solution

1. Step 1: Understand what #REF! means

   #REF! error usually means the reference is invalid or missing.

2. Step 2: Check if the referenced sheet exists

   If the sheet named 'Sales' does not exist, the formula cannot find it, causing #REF!.

3. Final Answer:

   The sheet named 'Sales' does not exist -> Option C

4. Quick Check:

   #REF! = Missing or invalid reference [OK]

Hint: Check if referenced sheet exists to fix #REF! [OK]

Common Mistakes:

• Assuming empty cell causes #REF!
• Adding quotes unnecessarily when no spaces
• Mistaking #REF! for syntax error

5. You want to create a summary sheet that shows the total sales from multiple sheets named Jan, Feb, and Mar. Which formula correctly sums cell D10 from all three sheets?

hard

A. =SUM(Jan:Mar!D10)

B. =Jan!D10 + Feb!D10 + Mar!D10

C. =SUM('Jan','Feb','Mar'!D10)

D. =SUM(Jan!D10:Mar!D10)

Solution

1. Step 1: Understand cross-sheet range syntax

   Google Sheets supports 3D ranges like Jan:Mar!D10 only if the sheets are consecutive tabs named Jan, Feb, Mar in order.

2. Step 2: Sum individual references explicitly

   Adding each sheet's cell individually like =Jan!D10 + Feb!D10 + Mar!D10 works reliably regardless of tab order.

3. Final Answer:

   =Jan!D10 + Feb!D10 + Mar!D10 -> Option B

4. Quick Check:

   Sum separate sheet cells by adding each reference [OK]

Hint: Add each sheet's cell separately to sum across sheets [OK]

Common Mistakes:

• Using invalid 3D range syntax
• Trying to sum sheets with commas inside SUM
• Assuming colon works for non-adjacent sheets