Managing and Reporting on Multiple Projects Effectively
I apologize for the length of my post, but I want to be thorough in describing what I would like to do and what I’ve already tried. I’ve also searched through help and the forums, and I either haven’t found a solution or am too hyperfocused on what I’m trying to do that I’ve missed it.
I’m looking for a way to manage and report on multiple projects efficiently. My overarching objective is to reduce the amount of time my team spends creating and updating project plans and reporting on them to various stakeholders. I think I know what I’d like this to look like, but I’m not sure Smartsheet has the ability to do it. It’s also very possible that I don’t know how to use Smartsheet in the most efficient manner.
At the direction of our leadership, we’ve been using an interpretation of what we think Smartsheet should do. We’ve created iterations of templates that were intended to work for everyone but instead work poorly. Updating projects is tedious, and reporting on them is laborious. Some managers have copied and pasted rows from individual project sheets into a separate “master sheet.” Others have used the native report feature to consolidate data…and then export it to Excel and/or create a PowerPoint presentation because the native reports can’t be formatted nicely.
I have two main requirements:
A. Ability to quickly add and update tasks across multiple projects in one view either by Project Lead or by Resource (i.e., the person assigned to the task). I like the visual nature of Card View and the ability to drag cards between lanes (e.g., from “In Progress” to “Completed”).
- Project Leads can own multiple projects.
- Resources can have tasks in multiple projects.
- Project Leads can also be resources.
B. Create a report that displays up to three levels—projects, milestones/deliverables, and tasks, with the number of levels determined by the target audience:
- Senior executives = project statuses and summaries
- Mid-level executives = project and milestones/deliverable statuses
- Immediate managers = project, milestone/deliverable, and task statuses
However, Card View (and Smartsheet in general) has limitations, some of which I discuss in my approaches below. One of the most annoying limitations is that Smartsheet can’t auto-populate cells with a static value when something changes, (e.g., enter the current date when a task is dragged from “In Progress” to “Completed”). This seems like something that could be easily handled through a workflow. I’ve considered using the modified date from the system, but if I were to edit a comments field two weeks later, the date would change.
I’ve tried these approaches:
1. Separate sheet for each project with indented hierarchy—Level 1 Milestone/Deliverable and Level 2 = Task.
- While I’m able to update cards when viewing Level 1 or Level 2, I can only add new cards at Level 1. I’m unable to add a new task (Level 2) unless I view Level 1 cards and add it as a subtask.
- This also doesn’t satisfy requirement (A) since I’m unable to see tasks for all projects in a single view. This is an absolute deal breaker for me.
2. Single sheet for all projects with indented hierarchy—Level 1 = Project, Level 2 = Milestone/Deliverable, Level 3 = Task.
- While I’m able to update cards when viewing Level 1, 2, or 3, I can only add new cards at Level 1. I’m unable to add a new task (Level 3) unless I view Level 2 cards and add it as a subtask. Likewise, I have to view Level 1 cards to add a Level 2 card as a subtask.
- Maybe I’m being too picky with having to click on higher level cards to add subtasks instead of just clicking a “+” icon in the level I’m currently viewing, but switching between levels is annoying.
3. One sheet for top-level project info, and another sheet for tasks.
- I linked the sheets using the ddupdate.com app to populate and synchronize drop-down lists between sheets. (Why is this not a standard feature like Data Validation in Excel?!) This is a little clumsy to set up because of the non-native functionality, and I had to create a way to build unique project identifiers that make sense.
- It’s not perfect, but it works…for two levels. Adding in the middle level of Milestone/Deliverable leads me back to the same challenges as (1) and (2) when adding new tasks.
- I could use ddupdate to populate two drop-down lists, but, as far as I can tell, I can’t use it to create cascading drop-down lists (e.g., after selecting Project 1 in the Project drop-down list, populate the Milestone/Deliverable drop-down list with only those milestones/deliverables that pertain to Project 1).
4. Same as (2), but instead of indented hierarchy, I used separate columns for Project, Milestone/Deliverable, and Task (Task is Primary Column). I just started playing with this approach, so I don’t have much to say at this time. My initial concern is the ability (or lack thereof) to roll up lower levels into higher levels.
- I’m not fond of the native reporting capabilities. Combining sheets into a single report loses hierarchy information. The lack of a proper tool that could kick out a nicely formatted report is also disappointing. I use several other applications that produce at least decent PDFs that I can attach to emails or present in a meeting.
- Fortunately, we have an Enterprise License, so I can use Access to build queries and reports using Smartsheet as the data sources (sheet = table).
Thank you for taking the time to read this. I appreciate any feedback and suggestions.