Counting completion status off of a group of sheets

Mike Wilday

We started a new process for course development that has tracking development of each of our courses in separate sheets. 1 course per sheet. Its easy to pull a report of how many of those courses are now in a complete status but I am trying to figure out how to pull together a dynamic number for the total number of courses in that report. Or, depending on how you look at it, the total number of completed courses from each of those individual sheets. Is there a way in smartsheet to do this easily? Recommendations? I don't want to build out a thousand cross sheet references for each individual sheet. There has to be a way a to do this that I am not thinking about? 😓

Genevieve P.

Hey Mike,

The way I would do this is multiple cross-sheet formulas... but maybe there's an alternative.

Could you build a workflow in each sheet that copies the row that marks when a project is completed? You could copy this into one big data collection sheet that just keeps the collected copied rows (this would probably look like your Report, but built in a sheet instead).

Then it would be one simple formula in that sheet to COUNT (or COUNTIF completed, should you need to adjust this data sheet).

Thoughts?

Genevieve P.

Hey Mike,

The way I would do this is multiple cross-sheet formulas... but maybe there's an alternative.

Could you build a workflow in each sheet that copies the row that marks when a project is completed? You could copy this into one big data collection sheet that just keeps the collected copied rows (this would probably look like your Report, but built in a sheet instead).

Then it would be one simple formula in that sheet to COUNT (or COUNTIF completed, should you need to adjust this data sheet).

Thoughts?

Mike Wilday

That is a great idea. Thank you. The copy row automation makes a TON of sense. We'll implement that right away.

Genevieve P.

Great!! Glad I could help :)