Cross-sheet references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a cross-sheet SUM formula

You have two sheets: Sheet1 and Sheet2. In Sheet1, cells A1 to A3 contain numbers 10, 20, and 30 respectively. In Sheet2, cell B1 contains the formula =SUM(Sheet1!A1:A3). What is the value displayed in Sheet2!B1?

A Syntax Error

B 100

C 60

D 0

💡 Hint

Remember that Sheet1!A1:A3 refers to cells A1 to A3 in Sheet1.

The formula sums the values 10 + 20 + 30 from Sheet1 cells A1 to A3, resulting in 60.

❓ Function Choice

intermediate

Choosing the correct formula for cross-sheet average

You want to calculate the average of values in cells B2 to B6 on a sheet named Data from another sheet. Which formula will correctly do this in your current sheet?

A =AVERAGE('Data'!B2:B6)

B =AVERAGE(Data!B2:B6)

C =AVERAGE(Data!B2-B6)

D =AVERAGE(Data:B2:B6)

💡 Hint

Sheet names with no spaces can be used without quotes, but quotes are safer.

Option A uses correct syntax with quotes around the sheet name, which is required if the sheet name has special characters or spaces. Option A works only if the sheet name has no spaces or special characters, but B is safer and always correct.

🎯 Scenario

advanced

Fixing a broken cross-sheet reference after renaming a sheet

You have a formula in Sheet3 cell C5: =SUM(Sales!C1:C10). You renamed the sheet Sales to 2023 Sales. Now the formula shows a #REF! error. Which formula fixes the error?

A =SUM(Sales!C1:C10)

B =SUM(2023 Sales!C1:C10)

C =SUM('Sales 2023'!C1:C10)

D =SUM('2023 Sales'!C1:C10)

💡 Hint

Sheet names with spaces must be enclosed in single quotes.

After renaming the sheet to '2023 Sales', the formula must use single quotes around the sheet name to handle the space. Option D correctly updates the reference.

❓ data_analysis

advanced

Analyzing cross-sheet reference impact on data update

Sheet1 column A has numbers 1 to 5. Sheet2 cell B1 has formula =SUM(Sheet1!A1:A5). If you change Sheet1!A3 from 3 to 10, what happens to Sheet2!B1?

A The value in Sheet2!B1 updates automatically to 22

B The value in Sheet2!B1 remains the same until you refresh manually

C Sheet2!B1 value becomes 10

D Sheet2!B1 shows an error because of the change

💡 Hint

Formulas recalculate automatically when referenced data changes.

Changing a referenced cell value updates the formula result automatically. The sum changes from 1+2+3+4+5=15 to 1+2+10+4+5=22.

📊 Formula Result

expert

Result of INDIRECT with cross-sheet reference

In Sheet1 cell A1 you have the text Data. In the Data sheet, cells B1:B3 contain numbers 5, 10, 15 respectively. In Sheet3 cell C1 you enter the formula =SUM(INDIRECT(Sheet1!A1 & "!B1:B3")). What is the value in Sheet3!C1?

A Syntax Error

B 30

C 0

D #REF! error

💡 Hint

INDIRECT builds a reference from text. Check what A1 contains and what range it points to.

Sheet1!A1 contains 'Data', so INDIRECT('Data!B1:B3') refers to Sheet named Data cells B1 to B3. The sum of 5 + 10 + 15 is 30.

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