Cross-Sheet References & Reports

Why Cross-Sheet Connectivity Matters

Real work lives across multiple sheets. A project manager might have one sheet per project. A PMO might track 30 active projects. An operations team might have separate sheets for intake, execution, and archiving.

Without cross-sheet connectivity, each sheet is an island — data gets copied and pasted, reports are outdated within hours, and leadership sees last month's numbers. With it, your Smartsheet environment becomes a live, connected system where changes in one sheet propagate automatically to every view that references it.

Smartsheet provides three mechanisms: cross-sheet references (for formulas), cell links (for individual cell mirroring), and reports (for aggregated views).

Cross-Sheet References

A cross-sheet reference is a named pointer to a column or range in another sheet. Created once, used in any formula on the referencing sheet.

Creating a reference:

1. Click into a cell and start a formula
2. Click the cross-sheet reference icon (grid with arrow) in the formula bar
3. Select the source sheet and highlight the range you want to reference
4. Give it a descriptive name (e.g., "Project Alpha Tasks Status")
5. Click OK — the reference appears in curly braces: {Project Alpha Tasks Status}

Using in formulas:

// Count all "At Risk" tasks across a portfolio
=COUNTIF({Portfolio Status}, "At Risk")

// Sum budget for a department from a finance sheet
=SUMIFS({Finance Budget}, {Finance Department}, "Engineering")

// Look up a project manager's name
=VLOOKUP([Project ID]@row, {PM Roster Range}, 2, false)

Limits:

• Up to 100 distinct cross-sheet references per sheet
• References must be explicitly created (you can't type {SomeSheet} freehand)
• The referenced range can be an entire column or a specific range

Cell Links

A cell link creates a live, read-only mirror from a source cell to a destination cell. The destination automatically updates when the source changes — no formula needed.

Creating a cell link:

1. Right-click the destination cell
2. Select "Link from Cell in Another Sheet"
3. Navigate to the source sheet and click the source cell
4. Click OK — the destination cell shows the source value with a small link arrow

Key characteristics:

• One-directional: source → destination
• The destination cell is read-only (you can't type in it)
• Updates automatically when the source changes
• Shows a small arrow icon to indicate the linked status
• Limit: 500 inbound cell links per sheet

Best use case: Rolling up key metrics from project sheets to a portfolio summary. Each project PM manages their own sheet. The portfolio dashboard pulls live values (budget remaining, task count, % complete, status) from each project sheet via cell links. The PMO always sees current data without asking anyone for updates.

Reports

A Report is a live, cross-sheet view that aggregates rows from multiple sheets based on filter criteria. It does NOT duplicate data — it's a live window into source sheet data.

Types of Reports:

Row Report (most common): Shows individual rows from selected source sheets matching your filter criteria.

Sheet Summary Report: Aggregates Sheet Summary fields from multiple sheets into one view. Useful for portfolio-level KPI metrics.

Creating a Row Report:

1. Home → + New → Report
2. Select source sheets (or an entire workspace)
3. Choose columns to display
4. Set filter criteria: "Status = At Risk" or "Assigned To = Current User" or "Due Date is in the next 7 days"
5. Configure grouping and sorting
6. Save and name the report

Report filters support:

• Equals / Does Not Equal
• Contains / Does Not Contain
• Is Before / Is After (for dates)
• Is Blank / Is Not Blank
• Is Current User (dynamic — always filters to the logged-in person)
• Is in the Last / Next N days (rolling date windows)

Using Reports as Data Sources:

• Embed in dashboards (see Lesson 5)
• Use as the data source for dashboard chart widgets
• Share with stakeholders who don't need sheet access

Architecture Patterns

Hub-and-Spoke

One master summary sheet (hub) pulls key metrics from multiple project sheets (spokes) via cell links. The master sheet feeds an executive dashboard.

[Project A Sheet] ─cell links─→ [Portfolio Summary Sheet] → [Dashboard]
[Project B Sheet] ─cell links─↗
[Project C Sheet] ─cell links─↗

Report-Driven Aggregation

Skip the summary sheet. Create a report that pulls from all project sheets, then use the report as the dashboard's data source. Simpler to maintain, automatically picks up new project sheets you add to the workspace.

[Project A Sheet] ─┐
[Project B Sheet] ─┼→ [Portfolio Report] → [Dashboard]
[Project C Sheet] ─┘

Workspace Structure

Organize related sheets into Workspaces (folder-like containers). Use Workspace-level reports to aggregate everything within a workspace. Sharing a workspace gives collaborators access to all contents.

Cross-Sheet Formulas in Practice

// Count all high-priority at-risk tasks across the portfolio
=COUNTIFS({Portfolio Status}, "At Risk", {Portfolio Priority}, "High")

// Sum the budget for a specific department
=SUMIFS({Budget Range}, {Department Range}, "Engineering")

// Look up a project manager by project ID
=INDEX({PM Name Range}, MATCH([Project ID]@row, {PM Project ID Range}, 0))

// Check if a product SKU exists in the master catalog
=IFERROR(VLOOKUP([SKU]@row, {Product Catalog}, 1, false), "Not in catalog")