Cross-sheet references in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a summary sheet that shows total sales per product category by month, using data stored in separate monthly sheets.

📊 Data: You have three sheets named 'Jan', 'Feb', and 'Mar'. Each sheet lists sales transactions with columns: Product, Category, and Sales Amount.

🎯 Deliverable: Create a summary sheet that uses cross-sheet references to calculate total sales for each category for January, February, and March.

Sample Data

Product	Category	Sales Amount
Apples	Fruits	100
Bananas	Fruits	150
Carrots	Vegetables	80
Broccoli	Vegetables	120

1

Step 1: Create a new sheet named 'Summary' to hold the total sales per category by month.

No formula needed for this step.

Expected Result

A blank 'Summary' sheet is ready for data.

2

Step 2: In the 'Summary' sheet, list the product categories in column A starting from A2: Fruits, Vegetables.

Enter manually: A2='Fruits', A3='Vegetables'.

Expected Result

Categories 'Fruits' and 'Vegetables' appear in cells A2 and A3.

3

Step 3: In the 'Summary' sheet, add month names in cells B1, C1, and D1: Jan, Feb, Mar.

Enter manually: B1='Jan', C1='Feb', D1='Mar'.

Expected Result

Months 'Jan', 'Feb', and 'Mar' appear in cells B1, C1, and D1.

4

Step 4: Calculate total sales for 'Fruits' in January by summing sales from the 'Jan' sheet where Category is 'Fruits'.

=SUMIF(Jan!B:B, A2, Jan!C:C)

Expected Result

Cell B2 shows 250 (100 + 150).

5

Step 5: Calculate total sales for 'Vegetables' in January similarly.

=SUMIF(Jan!B:B, A3, Jan!C:C)

Expected Result

Cell B3 shows 200 (80 + 120).

6

Step 6: Repeat the SUMIF formula for February sales in column C using the 'Feb' sheet.

In C2: =SUMIF(Feb!B:B, A2, Feb!C:C) In C3: =SUMIF(Feb!B:B, A3, Feb!C:C)

Expected Result

Cells C2 and C3 show total sales for Fruits and Vegetables in February.

7

Step 7: Repeat the SUMIF formula for March sales in column D using the 'Mar' sheet.

In D2: =SUMIF(Mar!B:B, A2, Mar!C:C) In D3: =SUMIF(Mar!B:B, A3, Mar!C:C)

Expected Result

Cells D2 and D3 show total sales for Fruits and Vegetables in March.

8

Step 8: Verify all formulas correctly pull data from the respective sheets and display totals in the 'Summary' sheet.

No new formula; check previous results.

Expected Result

Summary sheet shows total sales per category for each month.

Final Result

Summary Sheet

      |  Jan  |  Feb  |  Mar  
--------------------------------
Fruits   |  250  |  300  |  280  
Vegetables |  200  |  220  |  210  

✓Fruits consistently have higher sales than Vegetables each month.

✓February shows the highest sales for both categories.

Bonus Challenge

Add a total sales column that sums sales across all three months for each category.

Show Hint

Use the SUM function across columns B to D, e.g., =SUM(B2:D2) for Fruits.

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