Status bar help

Hello all,

I am pretty new to using Smartsheet but I am loving all of the functionality that has come with it. However I seem to be stuck on the formula for the blue status bar at the top level. For the child items in my example the bar simply fills up as soon as the completed box is checked, which is what I was going. My problem is I cannot figure out how to use the different stages of the status bar as each month is check off from Empty, Quarter, Half, Three Quarter, and Full.

Any help is appreciated

Thanks,

Sean


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I follow. Are you trying to write a formula to automate the parent row?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Yes, I have the formula set up that will fill the check mark at the parent row only when the rest are checked but I wanted to use the status bar as well in order for the parent row to track the progress.

    So for each child row I have =IF(Complete@row = 1, "Full") which will only fill the bar once the box is checked for the child rows.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So is it always going to be the 12 children, or could the number of children vary?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • The number of the children could vary but no more then 12, just depends what section of the plan I am plugging the formula into.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    OK. this can be done in a single formula, but that could very quickly become a bit of a monster. I am going to suggest using a helper column that will assign a score to each of the child rows. We then average those scores and use a ROUNDDOWN function to round down to one of those scores and finally use another formula to convert the parent score back into a status bar.


    Helper Column Child Rows:

    =IF(Status@row = "Full", 4, IF(Status@row = "Three Quarter", 3, IF(Status@row = "Half", 2, IF(Status@row = "Quarter", 1, 0))))


    Helper Column Parent Row:

    =ROUNDDOWN(AVG(CHILDREN()))


    Status Column Parent Rows:

    =IF([Helper Column]@row = 0, "Empty", IF([Helper Column]@row = 1, "Quarter", IF([Helper Column]@row = 2, "Half", IF([Helper Column]@row = 3, "Three Quarter", "Full"))))



    There are a few other ways to do this, but this would be one of the easier builds in my opinion.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you Paul,

    Would you put each of these into the respective columns? For example the ones marked "helper column parent row" would go into the helper column in the green row from the example image I posted originally?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That is correct. And the "Status Column Parent Rows" would go in the green row in the column where the status bar is.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ok thank you, I really appreciate the help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!