Summary Status

Gaurav Chauhan

Hi, I am trying to populate the overall status of a Project on to the Dashboard. So lets say, these are the 4 status types.

How do I populate a rollup status in a secondary sheet with the help of formuale wherein, if any of the statuses are "Complete", "In Progress" Or Not Started", then it says On Track but if any of the status says "delayed", then populate the status is "Delayed"

Jemari

I will start off by saying I am NOT an expert in smartsheet, but this is my 2 cents...

• If the status column is manually populated on a per-row basis, you will be best to manually populate the overall summary status as well.
• If it is populated based on logic, post that here and we can look at how to do a summary row calculation.

The conventional methods I have seen are (and I am not a fluent SS user so there may be others):

• Start with heirarchy rollup. If you indent a row, it becomes a child of the row above. The % complete in the parent row is a calculation of all the combined child rows. You can have one row at the top of your sheet and every row below that is a child which "sums" up everything.
• Consider creating locked rows at the top of the sheet that show the originally scheduled start and end dates of the project. These are separate from and do not change based on the logic in the individual project milestone & task rows below, and it gives you a baseline to see if dates are creeping based on updated start and end dates. You can include milestone rows as well. This can also be accomplished with a "due date" column that is locked such that only sheet admins can edit/change it which provides same thing on a per row basis, however I find the column approach is more granular than needed, and it annoys the team when they cannot edit this in-row.
• Use a schedule duration or variance formula in the row using networkdays, and create a schedule health or status based on the returned value. There are MANY ways to do this depending on your objectives, your data, and your abilities with formula complexity. In a nut shell you are looking at the duration using the start and end date for the entire project, and the percent of those that have passed since the schedule start date, and comparing that percent to the overall percent complete. Ex: project is 20 work days, we are 10 days in (50% of the duration), and the percent complete is 50% or more = Green/On Track. For yellow and red, the threshold is a calculation, and that is up to you what the logic thresholds are to trigger yellow and red. Note that your project settings will influence the calculation when using smartsheet functions in formulas. The obvious con to this are some individual rows are not linear in progress. Something might stay at 50% complete for months, and then leap to 100% when a contract is signed for example. This calculation is a general schedule health barometer, and not a crystal ball, so if people understand it, you will be fine.

I have a "status" column that the accountable party updates manually (qualitative), and I also have a "schedule health" column that is locked and updates based on a formula that follows my third bullet above (quantitative). I use the schedule health column to create the overall status roll up. On a per row basis it allows me to see rows that may be off track and discuss them with the various stakeholders.

Gaurav Chauhan

Thank you for the elaborate response.

I can create a helper column to capture the summary level Status. Basically I am trying to look for a IF statement formulae which says

If all tasks are marked complete, then "Complete"

If All Tasks are marked "Not Started", then "Not Started"

if any task is marked delayed, then "Delayed"

and if any task is marked In progress and none other as Delayed then "In Progress"