0
0
Google Sheetsspreadsheet~8 mins

Cross-sheet references in Google Sheets - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Cross-sheet references
Goal

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

Sample Data

Sheet1: Sales Data

ProductSales
Apples100
Bananas150
Cherries200
Dates120
Elderberries80

Sheet2: Summary

ProductTotal 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.