Solution Details:

• Project metadata entry – key project information (project name, project manager and description) can easily be added using the Sheet Summary and this will appear on the project dashboard.

• Automatic task health calculation – task health is calculated automatically based on dates and completion (Red = overdue or blocked, Yellow = due in the next 2 days, Green = due in more 2 days, Blue = complete). The parent tasks are calculated based on the status of the children rows.  The formula can be switched off by sheet admins so it can be manually selected or the formula changed to reflect the requirements of the organisation.

• Automatic project metrics calculation – the number of tasks (excludes parent rows), and the number of tasks in progress, not started, complete or blocked is also calculated so it can be added to the dashboard if required (we have left it off deliberately)

• Tasks at risk report – any task that is either overdue or marked as ‘Blocked’ appears on the Tasks At Risk report, which shows on the dashboard.

• Summary Schedule – key tasks can be selected to appear on a summary schedule, providing a more digestible view of key timings.

• Automatic hierarchy visual navigation – parent rows are automatically colour-coded to aid visual navigation (they can also be collapsed and expanded to aid navigation).

• Progress automatically calculated – task completion is indicated as % Complete. For tasks at 0%, they show as ‘Not Started’ and as ‘In Progress when between 1%-99% Complete. The % Complete for parent rows and the overall project is automatically calculated based on the tasks.

• Predecessors, dependencies and critical path enabled – tasks can be set as successors or predecessors so the dates are automatically calculated based on the preceding task, and lead or lag timings can be added. The critical path is automatically calculated based on tasks with predecessors.

• % Allocation and Workload Management – Workload management can be enabled and the % Allocation column used to calculate capacity.

• Blocked tasks flag – tasks can be flagged as Blocked, which changes the Health to Red and indicated Progress as ‘Blocked’

• Project and task status commentary with automatic date recording – task owners can provide progress commentary and the date updated is automatically recorded in the next column. The overall project commentary from Row 1 is included on the project dashboard.

• Automations – currently deactivated, two automations in addition to the commentary update time-stamping are included as follows, noting these can be amended and added to as needed:

  • Weekly project status update – this will be sent weekly on Fridays to the project owner when activated (this could be modified / duplicated to send to all task owners if appropriate). The project owner will get this by email and can simply update the email update request, or respond to the notification in Smartsheet.

  • Update request for overdue tasks – an update request will be sent to task owners when a task becomes overdue. As above, this will be sent by email and also within the users notification centre.

All other standard Smartsheet features are included within this solution by default, such as the ability to view sheet history and highlight changes, view the sheet in alternative views such as Board (Kanban) view, and this solution can be modified accordingly. There are also a few hidden columns that come in handy with the solution.