Seeking Guidance on Connecting Biweekly Status Reports to Projects and Dashboard Creation

lauren_h

Hey Smartsheet Community!

I hope you're all doing well. I'm currently working on optimizing our project management process using Smartsheet and could really use some guidance.

Objective:

I work for a PMO for state government. We're managing project updates through a biweekly (due every other week) "Status Report" form and collecting project requests via the "Project" sheet which also has an intake form. Now, the challenge is to establish a seamless connection between the submitted status reports and their corresponding projects in the "Project" sheet. Additionally, we're looking to create a dashboard that can efficiently monitor and assess the overall health of all projects based on the information gathered from these status reports.

Specific Questions:

1. What's the best way to link biweekly status reports to specific projects in the "Project" sheet?
2. Are there any best practices to ensure alignment between project information from the "Project Intake" form and the corresponding status reports?
3. Any tips on creating a dynamic dashboard in Smartsheet that provides visual insights into the health of all projects based on the collected status reports?

Any guidance or insights you can share would be immensely helpful! TYSM in advance for your support.

Andrée Starå

Hi @lauren_h

I hope you're well and safe!

Are you using Control Center?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lauren_h

Hi @Andrée Starå ,

I am not using Control Center. We do have access to Control Center but do not have it configured because the people that need to submit status reports do not have Smartsheet licenses so they wouldn't be able to create artifacts in Smartsheet.

Genevieve P.

Hi @lauren_h

1) As long as your projects all have a specific, unique identifier (e.g. a unique name or ID) then you can use an INDEX(COLLECT formula to return the most recent (top-down) submission on the second sheet. You'll need to sort the sheet so new entries are at the TOP so the formula pulls back the most recent one.

Try something like this:

=INDEX(COLLECT({Status Column}, {Unique Project ID}, [Unique Project ID]@row), 1)

The 1 at the end tells the formula to bring back the first matching row it finds, hence needing the Sort to be newest-to-oldest. Here's more information: Formula combinations for cross sheet references

2) Another way to track data across Projects would be to use a Report with the two sheets as the source, then Group the Report by the unique Project ID (as long as the overall Project sheet and the Intake sheet have the same Project ID column name and type). This will gather all the related/relevant rows in one place, under the Project ID header. You could then filter the report by a specific date if you only want rows from the last month, etc. See: Redesigned Reports with Grouping and Summary Functions

3) I would use formulas in a separate metrics sheet to create calculations and display that in a chart. For example, you can count how many rows are in a specific Status:

=COUNTIF({Status Column Main Sheet}, "In Progress")

Then you could do a Donut Chart showing the different status counts and progression of projects. There are some template sets with Dashboard examples that you may find helpful, for example: Project Tracking and Rollup

Cheers,

Genevieve

Brian_Richardson

Hi Lauren - you don't need a Smartsheet license to submit status reports or update cells on an existing sheet. So for example, for our project status reporting we have a standing status sheet for each project, with a number of status elements, that the PM simply updates on a weekly basis to the current accurate values. No need to deal with submitted forms.

Andree asked about Control Center as it's designed to link intake and metrics together for dashboarding. We also use Control Center and I highly recommend it, particularly if you're running more than about 20-30 projects. The other thing you get from CC is pushed changes...so when you decide your projects should have an extra column in the schedule, you can push that out rather than touching each and every project schedule sheet.

However, to answer your initial questions:

If you are having people submit a status report through a form, then (as I'm sure you know) the form will create a new row for each status report. You'll have to find a way for the submitter to identify the project they are submitting for. One way would be to have a "Project Name" dropdown field that updates with the current list of projects. If you have Bridge, there's a pretty easy way to do this dynamically, instructions here. If you have Data Shuttle but not Bridge, then you can use Data Shuttle to do this too. It's a little awkward, but the steps are outlined on this website. If you have neither addon, then I recommend getting them :-) Once you have people picking project names, or maybe IDs from a dropdown, then you have a reference point to link with.

To reference the latest status update from another sheet, you'll need a couple of helper columns in the sheet that collects the status updates. Setup an AutoNumber column (let's call it "Status Number"). Setup a checkbox column (let's call it "Latest Status") with the formula

=IF([Status Number]@row = MAX(COLLECT([Status Number]:[Status Number], Project:Project, Project@row)), true)

This formula will mark the checkbox on the row that has the latest status for each project.

Then from another sheet you can reference this sheet in a cross-sheet lookup formula

=INDEX(COLLECT({Status Reports-Status}, {Status Reports-Project}, [Project Name]@row, {Status Reports-Latest Status}, true), 1)

This formula "collects" the Status column, matching the project name and finding the "Latest Status" checkbox. That collection will only have 1 row in it, which is then returned. If you haven't used cross-sheet references before, you insert them by clicking the "Reference another sheet" link while typing the formula. Each cross-sheet reference is the entire named column (ie "Status Reports-Project" is the Project column from the Status Reports sheet).

You can repeat this process for other columns of information, not just status.

Once you have a sheet with all the latest information on it, you can use that sheet to create dashboards. Typically you'll want to setup reports that point at that sheet and bring together information for charting or viewing in a more friendly way. For charting, you can use the report Group and Summary functions to count projects in certain statuses, as an example, and then chart those. If you group by Latest Status and use Summary to Count those Latest Statuses, you'll get a report that tells you you have 1 project in Good and 1 project in Bad status. You can then chart the report, and it will always be up to date without having to constantly adjust your chart ranges as you add more projects.