Dashboard Widget - Deliverable Type by Project

mmac

Hello,

 

I am trying to determine how best to display deliverable types by project. I would like to display a widget within a project dashboard. Hoping to capture the following as an example.

 

If there are three projects and 6 deliverable types that are required for each project to continue forward, the widget would display all deliverable types at a project level. I've attached screen shots of the project sheet, which holds the data, a metrics sheet which would essentially pull numeric values into the dashboard, as well as a view of the dashboard. 

 

I am hoping for further insight and support on how to manipulate the metric sheet or master project sheet to then be able to display a widget in the project dashboard.

 

If there is further information I can provide to gain further assistance, please let me know. Thank you!

Isaac Jose

Hello mmac,

Thanks for your post! Everything you have setup so far looks good. Are all three projects in one sheet, or are they in three different sheets? If they're all in the same sheet, I recommend inserting a "Project Name" column and using a COUNTIFS formula in your metrics sheet so that you can count rows that meet multiple criteria (IE "Project Name" and "Deliverable Type"). Once you've inserted the Project Name for each row on the project sheet and metrics sheet, use the following formula in your Metrics sheet:

=COUNTIFS({Project Name Range}, [Project Name]@row, {Delivery Type Range}, Category@row)

This formula will count the number of rows in your Project Sheet that have the same Project Name and Delivery Type / Category as the metric you're tracking in the Metrics Sheet. Once you have the metrics for all three projects, I would recommend creating three separate Chart Widget so that each project has it's own widget. You could use a single Chart Widget, but your Chart may be difficult to process if there are too many data points.

If the projects are in three separate sheets, the process is similar, but your formulas will be slightly different. In this case, you could use COUNTIF, but you'll need different Cross Sheet References in your formula depending on which project sheet you're trying to summarize. I've linked some additional resources for this below:

• COUNTIFS https://help.smartsheet.com/function/countifs
• Using @row and @cellhttps://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
• Cross Sheet Formula Info https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
• Advanced Cross Sheet Formulas https://help.smartsheet.com/learning-track/smartsheet-advanced/advanced-cross-sheet-formulas

I hope this helps!

Kind regards,

Isaac J

Smartsheet Support