Overall Project Status

Brian Davis

Hey Everyone,

I'm trying to create an overall project status similar to the one displayed in the "Maximize Your Smartsheet Experience" webinar. https://www.smartsheet.com/videos/maximize-your-smartsheet-experience?frame=0&nav=1



Around timestamp 4:50, a task status is updated from red to green via an update request.



Once the status update is submitted and the sheet refreshed, the overall sheet status has automatically changed from Red to Green. This is the automation I am looking to add to my project sheets.

Reason for the automation is similar to the reason in the video. I've created a dashboard for my supervisor to access and quickly view the overall health of my projects.

If anyone has ideas or additional information on this type of automation, please let me know.

Thanks,

Brian

Shaine Greenwood

Hello Brian,

This type of automation is performed with a nested IF statement, which is multiple IF formulas placed inside one another to change return value based on conditions.

• Details on creating formulas in Smartsheet can be found in the help center: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet
• IF function information is available here: https://help.smartsheet.com/function/if
• Our complete functions list is here: https://help.smartsheet.com/functions

An example of a nested IF that can change RYG balls is as follows:

=IF(COUNTIF(CHILDREN(), "Red") > 5, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 10, "Yellow", "Green"))

The above formula will change a parent RYG ball to "Red" if the count of the "Red" children is greater than 5, "Yellow" if the count of the "Yellow" children is greater than 10, otherwise (if neither condition is met) then it changes to "Green."

After creating the nested IF statement relevant to your project health/status, you can add this data as a metric widget to a dashboard. More information on metric widgets can be found here: https://help.smartsheet.com/articles/518558-widget-types-for-smartsheet-dashboards#metric

 

Brian Davis

Hi Shaine,

Thank you for the information. I was able to insert the formula and it worked.

Is it possible to have "Red" show up if the count of "Red" is greater than or equal to the count of "Green"?

This is more of what I'm looking for in this formula and functionality. So that if the number green balls is greater than either yellow or red, the overall status of this parent row is green. Additionally, similar formula for yellow and red with the resulting ball representing whichever color is represented at a higher number.

I tried a COUNTIF formula and as a single formula it worked but then I couldn't get the COUNTIF formulas to work against each other to provide a result for any color. See below.

=IF(CHILDREN()(COUNTIF(CHILDREN(), "Green")) > ((COUNTIF(CHILDREN(), "Yellow"))), "Green")

In my head this reads as "if the children count of green is greater than the children count of yellow, then green is the answer" but I keep having #UNPARSEABLE displayed.

In addition to figuring out how a parent role can display a status of its children based the which status is displayed more, I am trying to figure out how to display an overall status based on the collective parent rows.

Any additional thoughts are GREATLY appreciated.

Brian D.