Welcome!

Overall Project Status

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

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Not enough space in the cell for my notes
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…
how can i overlap 2 tasks.
Hello, i've a question about 2 tasks. Task 1 has for example 4 hours and should be on day 1 for example 6 january. Task 2 has 30 hours and should be on start on 4 january and futher. How can i manage that on 6 january task 2 has only 4 hours? Now i got the message user is over-allocated because task 2 is planned on a full…
Update Automations
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…