Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
We use a Project Timeline Template that tracks the progress of each individual project. There are a number of parent rows with child rows beneath. I've created a status column so that each task can reflect the current progress via a harvey ball. I would like the parent rows to reflect the overall current status based on the average of its children's status. Is this possible to do via conditional formatting?
Emily,
No directly with Conditional Formatting.
Conditional Formatting is display, not calculations.
What you'll need to do is convert the Harvey Balls to numbers like this:
=COUNTIF(CHILDREN(), "Quarter")*25
That counts the number of children that have the quarter fill and multiplies by 25.
You will need to sum all the different possibiltiies and divide by the total
shorhand CC is COUNTIF(CHILDREN())
(CC-quarter*25 + CC-half*50 + etc...) / (CC-empty+CC-quarter+... etc...)
nasty but possible.
I would add another column and put this in the children
pseudo code:
if harvery ball quarter = 25
else if harvey ball half = 50
etc...
and then in the parent
=sum(children()) / count(children())
and the run your Conditional Formatting off that.
Craig
Thank you, Craig, for the very thorough explanation! Very much appreciated.
I will give this a try tomorrow and see how it works...I may come back to you with more questions. Hope that's ok!
No problem. If I have spare minutes, I often check here.
