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.
mother cell status
Hello everybody
I'm looking for a formula for a parent cell status with a set of children rows
The status of the mother cell should be green if all children are green.
It must be gray if all the children are gray
It must be yellow if at least one of the children is yellow or gray…
the formula must give the expected results as in the attached image
Thank you in advance for your help
Comments
-
Use an IF statement with the Children statement in the formula?
-
how can I do that ?
Thanks
-
Hello Abdel,
Thanks for the question. Jeff K is correct that this can be accomplished using a nested IF formula and the CHILDREN() function, however it can be a little difficult to get it fully written out to do everything you'd like. The trick to nested IF formulas, is that these formulas will stop and give the result for the first true statement, going from left to right. Here's how this formula can be written to meet all of the criteria it looks like you're looking for:
=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), <>"-") = COUNTIF(CHILDREN(), "Gray"), COUNTIF(CHILDREN(), <>"-") <> 0), "Gray", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Gray") > 0), "Yellow", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "-"), "-", IF(COUNTIF(CHILDREN(), <>"-") = COUNTIF(CHILDREN(), "Green"), "Green")))))
This formula can be placed in every parent row in the status column. To learn more on nested IF formulas, here's a blog post with more about this (https://www.smartsheet.com/blog/support-tip-build-nested-IF). To learn more about the different functions used in this formula, here's our functions list help center article (https://help.smartsheet.com/functions).
-
Thank you Robert !!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives