IF formula on parent line for if a child has a value

I have a child parent relationship set up with an Ancestor column. I want the top Ancestor Line to fill in "Yes" in the "NDA" column if any child line has the contract type NDA

Best Answers

  • Emily McNeeley
    Emily McNeeley ✭✭✭✭✭
    Answer ✓

    @Heather Duff

    That fixed it. Now I realize that I may have multiple parent lines, any way to update this so that if the child line says Yes in NDA that the parent does too?

    So here the grandparent Microsoft line is blank

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Emily McNeeley Easy peasy! So that you don't have to do a bunch of different formulas, you could do something like this:

    =IF(COUNTIF(CHILDREN(),"Yes")>0,"Yes",IF(COUNTIF(CHILDREN([Contract Type]@row), HAS(@cell, "NDA")) > 0, "Yes", ""))

    You should then be able to use this formula in both parent and grandparent rows in the NDA column. It translates to:

    If any of the children of this row say "yes" in the NDA column, show Yes. Otherwise, if any of the children of this row have NDA in their Contract Type, show Yes. Otherwise, show blank.

    Let me know if that works.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!