Archived 2015 Posts

Archived 2015 Posts

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

✭✭
edited 12/09/19 in Archived 2015 Posts

Hi,

 

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

i.e:

          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.

 

Thanks

 

 

Comments

  • ✭✭✭✭✭✭

    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,

    Brett

  • ✭✭✭✭✭

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

  • ✭✭✭✭✭✭
    edited 09/15/15

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

  • ✭✭✭✭✭

    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."

     

     

  • ✭✭✭✭✭✭

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

  • ✭✭✭✭✭

    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"

     

    https://community.smartsheet.com/announcement/working-symbol-formulas

     

  • 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")

    I get #UNRECOGNIZED FUNCTION...

    What am I missing here?    

    Thanks!

     

     

  • ✭✭✭✭✭✭

    Elad,

    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

     

     

    -b

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

Trending Posts