Count from all project sheets in the same Workspace

Matt_W

We have multiple projects (25+) using the same template in a workspace. I am able to create a report that shows all of the upcoming task by adding the entire workspace to the report and creating the right filters. I want to output a count of all of these tasks and use that value in a dashboard setting.

Smartsheet does not seem to allow you to do a countif statement referencing a report.

Smartsheet also doesn't have the functionality to do a countif of all of the projects in a workspace. We are constantly adding and closing projects so I don't want to have to do this manually.

It would seem that my best solution is to manually do the count from a report file on a weekly basis. This seems overly burdensome.

Does anyone else have an idea?

ShelbyWarren

@Matt_W What I've done in the past is build the "Copy Row" automation into one of the sheets in your Template Folder, that sends the project data to an "All Projects Metric Rollup Sheet" (or something like that), once a week, or every day, whenever the project status is "Open". Then you can create a report of that sheet that only shows the most recent project data for each project that has an Open status.

Then every time the template folder is "Saved as new" it will have this automation built in.

(You can also use this to capture daily project statuses for the various metrics you'd like to see. I use the Copy Row function on every project sheet to copy to a "Daily Update Log" for the entire project when the Project Status is "Open" so I can see trends over time).