Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Making parent complete if children complete BY REFERENCING OTHER COLUMNS

    ✭✭
    in Formulas and Functions

    Hi. I know similar questions have been asked, but none seem to reference other columns, which is the key problem for me.

    Problem: In my sheet, the Health column symbol changes color based on End Date column UNLESS the Progress column is marked "Complete", in which case health turns green. However, as you can see in the screenshot below, this leaves the parent row red unless the parent row is also independently marked complete.

    Desired Outcome: I would like the parent row to *automatically* turn green if all of the children are marked complete. I've tried using the CHILDREN function for this but can't get it to work referencing child cells in other columns. I can only get it to work referencing child cells in the same column.

    Here is the formula I currently have for the health column:

    =IF(ISBLANK([End Date]@row), " ", (IF(AND([End Date]@row <= TODAY(), NOT(Progress@row = "Complete")), "Red", (IF(AND([End Date]@row <= TODAY(7), NOT(Progress@row = "Complete")), "Yellow", (IF(AND([Start Date]@row > TODAY(), NOT(Progress@row = "Complete")), "Gray", "Green")))))))

    Thanks in advance for any help!

    Parent Should Show Complete Screenshot 2021-06-29 164934.png


    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭✭
      Answer ✓

      Hi Brandon,

      You can reference the children in a different cell by doing CHILDREN(Progress@row). If you are wanting to determine value of Health based on the Progress of the children all being Complete, I've used a formula like this:

      IF((COUNTIF(CHILDREN(Progress@row), ="Complete")) = COUNT(CHILDREN(Progress@row)), "Green", "Red")

      It checks how many children are Complete. If that matches the number of children, that means all children are complete, then it should be green too -- if not, then it can go through the rest of your formula to determine what it should be.


      Hope this helps!

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      Answer ✓

      Hi Brandon,

      You can reference the children in a different cell by doing CHILDREN(Progress@row). If you are wanting to determine value of Health based on the Progress of the children all being Complete, I've used a formula like this:

      IF((COUNTIF(CHILDREN(Progress@row), ="Complete")) = COUNT(CHILDREN(Progress@row)), "Green", "Red")

      It checks how many children are Complete. If that matches the number of children, that means all children are complete, then it should be green too -- if not, then it can go through the rest of your formula to determine what it should be.


      Hope this helps!

      · Share on FacebookShare on Twitter
    • ✭✭

      That helped! Thank you, Victoria.

      Now on to make next ambitions for perfecting this formula.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions