Cross-sheet references in Google Sheets - Dashboard Guide

Dashboard Mode - Cross-sheet references

Goal

See total sales by product using data from a different sheet.

Sample Data

Sheet1: Sales Data

Product	Sales
Apples	100
Bananas	150
Cherries	200
Dates	120
Elderberries	80

Sheet2: Summary

Product	Total Sales
Apples
Bananas
Cherries
Dates
Elderberries

Dashboard Components

• Component 1: Product list in Sheet2 (A2:A6)
• Component 2: Total Sales per product in Sheet2 (B2:B6) using cross-sheet reference formula:
  =VLOOKUP(A2, 'Sheet1'!A:B, 2, FALSE)
  This formula looks up the product name in Sheet1 and returns its sales.
• Component 3: Total Sales Sum in Sheet2 (B8) using:
  =SUM(B2:B6)
  This shows total sales for all products combined.

Dashboard Layout

+----------------------+------------------+
| Product (Sheet2 A2:A6) | Total Sales (B2:B6) |
+----------------------+------------------+
| Apples               | 100              |
| Bananas              | 150              |
| Cherries             | 200              |
| Dates                | 120              |
| Elderberries         | 80               |
+----------------------+------------------+
|                      | Total: 650       |
+----------------------+------------------+

Interactivity

If you change sales numbers in Sheet1, the totals in Sheet2 update automatically because the formulas reference Sheet1 data.

You can add filters or dropdowns in Sheet2 to select products and see their sales dynamically.

Self Check

Add a filter in Sheet2 to show only products with sales above 120. Which rows update?

Answer: Rows for Bananas (150), Cherries (200) update to show; Apples, Dates, and Elderberries hide.

Key Result

Dashboard shows total sales per product using cross-sheet references from sales data in another sheet.

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