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

Emily B.
Emily B.
edited 12/09/19 in Archived 2016 Posts

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?



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 05/08/16



    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



    and then in the parent

    =sum(children()) / count(children())


    and the run your Conditional Formatting off that.






  • 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!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    No problem. If I have spare minutes, I often check here.



This discussion has been closed.