Dependency between multiple RYGG cells

Elad Cohen

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

Find more posts tagged with

Conditional Formatting

Project Management

Cell linking

Enhancement Request

Comments

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,

Brett

Jamison

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

Brett Evans

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

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

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

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"

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

Elad Cohen

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!

Brett Evans

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

Elad Cohen

Hey Brett,

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

All works like a charm thanks for the help.