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
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?

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/workingsymbolformulas

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.