Status bar help

Sean Austin

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

Smartsheet form.jpg

Paul Newcome

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

Sean Austin

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

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

Sean Austin

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

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.

Sean Austin

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

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

Sean Austin

Ok thank you, I really appreciate the help.

Paul Newcome

Happy to help. 👍️