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.

Dependency between multiple RYGG cells

Elad Cohen
edited 12/09/19 in Archived 2015 Posts



Is it possible to create a complex dependency such that a task's status is derived from it's sub-tasks' status.



Task  AVG(ST1-4)

  ST1    R

  ST2    G

  ST3    G

  ST4    Y

That way we could avoid manually updating task's status manually and also connect it between sheets.






  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    This is possible.


    1.  Add a hidden column ColorValue that assigns a number to each color RYG

    2.  Add a hidden column ParentValue

    3.  The ParentValue would  be equal to Round(Average(Children))

    4. The Parent Harvey Ball column would be a nested if statement that says if Parent value = 1 then Red, if 2 thenyellow, if 3 then green.


    Hope that helps,


  • Jamison
    Jamison ✭✭✭✭✭

    Would populating the ColorValue cell a manual task or can that happen automatically?

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    edited 09/15/15

    It would be like step 4.  But you would replace ParentValue with ColorValue.

  • Jamison
    Jamison ✭✭✭✭✭

    No, what I am asking is if the assigning a value step is one that has to be done manually.


    I got to the row, select a "yellow ball." - Do i then have to move to the next column and put a 2 in the column, or can I use a formula to assign the value e.e.g,  "if [status] = yellow, then ColorValue = 2."



  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    you got it, use a formula to assign the value e.e.g,  "if [status] = yellow, then ColorValue = 2." 

  • Jamison
    Jamison ✭✭✭✭✭

    Thank you! Here's another great post on these types of formulas that i found when i was trying to figure out why my formula was "Unparseable"




  • Thanks for the help!

    However, Im a noob here, and ran into some problems in asigning the RYGG colors from the formula output:


    What I did was using 2 columns:

    1. The status column

    2. The parent value column which assigns numerical value as a dependency of the color(works as expected):

      =IF(Status3 = "Gray", 0, IF(Status3 = "Red", 1, IF(Status3 = "Yellow", 2, IF(Status3 = "Green", 3, -1))))


    When I enter the formula to asign an RYGG color:

    =IF(ROUND(Average(ParentValue3:ParentValue6)) = 0, "Gray", "Yellow")


    What am I missing here?    




  • Brett Evans
    Brett Evans ✭✭✭✭✭✭


    Can you publish your sheet?  That would make it  easier to debug.


    Make a copy of the sheet with out any corporate secrets on it.

    Click on the publish tab at the bottom of the sheet

    Allow it to be edited by anyone

    share teh URL back here




  • Hey Brett,


    The problem was with my syntax.... Average instead of AVG, just got it.

    All works like a charm thanks for the help.


This discussion has been closed.