Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Conditional Formatting Question
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?
Comments
-
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!
-
Emily,
No problem. If I have spare minutes, I often check here.
Craig
