Managing and Reporting on Multiple Projects Effectively

Rob Ford

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.

5. Reporting

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

Debbie Sawyer

Hi Rob

Wow! What a post!

Have you experimented with Dashboards yet at all? These give you the ability to link to multiple projects/views from a central space and have a clear overview of lots of projects from lots of different view points.

You might find that you can produce some multisheet reports that are linked to the current user that could allow the user to update their tasks in one place, linkable from the Dashboard.

Conditional logic in forms is not yet in Smartsheet, at SBP we have developed a way of getting it working in Smartsheet but the solution is browser dependent and requires a certain method of data entry in the dropdown lists. Dynamic View within Smartsheet has some built in conditional logic though - do you have access to that at all?

You could potentially use forms to add new tasks then use helper columns to provide a logical way of sorting within your projects that would "sort"/"move" the newly added columns to the right place in your sheet? Ideas to play with.

I too come from a relational database background and found Smartsheet a little restrictive when I first started using it - but after using this product for nearly 4 years now I don't hold that view anymore. It is changing all the time and being enhanced all the time and once you know where the constraints are there are usually pretty creative ways to overcome them.

I'd be happy to zoom with you to discuss alternative ways of using the product to get what you want from it if that would help?

Kind regards

​Debbie Sawyer Consultant & Training Manager

Rob Ford

Thanks, Debbie!

COVID-19 has had me divert my attention to other things, and I'm finally coming back to this.

I haven't spent much time with dashboards, but I'll take another look. From Smartsheet's templates, it seemed that they would have comparable functionality as reports. Is it possible to have a card view of some sort in dashboards that would allow me to drag tasks between statuses and also create new tasks?

My company has not purchased/enabled Dynamic View for us. I was curious if that would have some of the functionality I'm looking for.

I looked at your "Conditional Logic on Smartsheet Forms" extension for Chrome. (We use chrome.) It looks very useful for solving other challenges our teams have had!

Yes, I'd be interested to Zoom with you to learn more. Let me know the best day and time and way to get in touch. Thank you.

Current status:

I have four separate sheets: 1) Project level, 2) Phase level (formerly Milestone/Deliverable), 3) Task level, and 4) Lookup (mainly for sort orders in reports, but also to be able to roll up dates and statuses). In general, Projects and Phases will be set more or less at the beginning of the year with 2-4 reviews during the year to add, edit, re-prioritize, etc.

Most of the updating will be done at the Task level. Through lookups to the Phase and Project levels and applying filters, I can get full picture of evertything someone I my team is responsible for, and we can very quickly update statuses, add notes, etc.

I think I can eventually figure out the reporting aspect. I'm starting with using the first three of my sheets as tables in Access via ODBC, then building queries and reports off of them. I plan to revisit this in the future to see what I can put together in reports and dashboards in Smartsheet.

Regards,

Rob Ford