The "Master Program View" (Dynamic Sheet Aggregation)

adoosa

Problem: Current Limitations

• Reports are "Flat" Lists: Current Reports can aggregate rows from multiple sheets, but they function as static lists. They cannot perform project logic (dependencies, duration calculations) across those sheets.
• No Cross-Sheet Critical Path: There is no native way to identify a "Critical Path" that spans across different teams' sheets. If Team A delays Team B, the system does not visualize this impact in a central view.
• Fragmented Visualization: You cannot generate a single, unified Gantt chart or timeline that combines 25+ separate team plans without manual and fragile cell-linking.
• Lack of "Roll-Up" Logic: There is no automatic way to calculate a weighted "Overall Project % Complete" based on the progress of tasks living in separate sub-sheets.

Proposal: The "Master Program" View

• Dynamic Aggregation: Create a new "Master Sheet" type that pulls in full project logic from sub-sheets, not just static row data.
• Unified Critical Path Analysis: Allow the system to calculate dependencies between sheets, enabling a top-down view of the critical path across the entire program.
• Two-Way Grouping Views:
  • By Team: A view that visualizes progress grouped by the owning team (Sheet A vs. Sheet B).
  • By Milestone: A view that stacks all tasks from all teams that contribute to a specific shared milestone (e.g., "All tasks linked to 'Phase 1 Delivery'").
• Smart Metric Roll-Ups: Enable parent-level metrics (status, health, % complete) to automatically calculate based on the real-time data of the underlying team sheets.

ccarriger

Are you relying on the sub-sheets for segmentation between teams or projects, then? Because Smartsheet will still need that datapoint somewhere in order to allow for filters or roll-ups to work as expected, so I'm just curious where that's currently happening.

I've moved to a row-based approach—basically seeing the row as the smallest trackable unit in Smartsheet and building out from there. I know some prefer to see the sheet as the smallest unit and start with their build from there but that's caused issues with various aggregation or tracking needs.

That means that I have one task sheet that can be rolled in to multiple reports as well as rolled up to a high-level project intake sheet for aggregation. Then in the task sheet, I build any additional reference data points that I need as columns and (as you mentioned) put in cross-sheet formulas to link and summarize. I actually like the cross-sheet formulas so that may be why it's not an issue to me :) But I've found that I like this approach better because I'm not segmenting my task data between sheets and then needing to aggregate it later from all those sources. I've got just one sheet and then reports or dynamic views that let me segment it for specific interactions like adding resolution notes, checking on overdue items, etc.