Hi Smartsheet community,
I would kindly ask for your best practices/recommendations to handle the following situation in a large company where we are using the PMO Template for managing IT Projects.
When we built the solution, we started with two main files:
- DataSource → List of demands and details, sourced from our ticketing system.
- MasterSheet → Detects if a new DemandID appears in the DataSource and, if so, adds it to the MasterSheet. The key columns are then populated using index/match.
The solution has been very successful, and other areas are now planning to adopt it. To support them, I created 5 MasterSheets (one per area) with DataShuttle automations that plug the DemandID into the corresponding sheet. For visibility, I built reports that consolidate data from these 5 files (all share the same structure and columns).
My question is about scalability:
Would it be better to merge these 5 MasterSheets into a single one? This would simplify referencing, reporting, and template usage, since everything would connect to one central MasterSheet. Having everything in one file would also make it easier to apply changes to the system (e.g., updating a column name once instead of in 5 different places).
However, I see some potential limitations:
- Different areas may eventually require custom columns for their own needs.
- Performance: combining everything into one sheet would mean ~500 rows, most of which are populated via index/match formulas. I’m concerned this could slow down the sheet.
- Permissions: generally, users don’t edit the MasterSheet (except for one or two columns to assign teams). But there are also cell links pulling data from project plans, which might further affect performance.
Has anyone experienced something similar? How did you handle it?
Thanks a lot in advance,
Pablo