Formula Help with Parent Function

Options

I need help creating a formula that references the name of the first parent row.

There are multiple parents and I want the "closest" parent name to populate in the Test column. Hierarchy Level is showing what I want to populate but has been added manually. All the brown and white headers below Module 1 Overview should have Module 1 Overview populate in test.


Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @kpierson109

    I have something similar setup. I believe this will work for you. The formula below will work as a Column formula, but starts at the first indent. If you want it to start at a lower ident, you need to update the > 0 to be the number indent you want to start at. So to start at the 2nd level on indent, then you want a 1 instead of a 0.

    =IF(COUNT(ANCESTORS([Public Facing Title]@row)) = 0, " ", IFERROR(IF(COUNT(CHILDREN([Public Facing Title]@row)) > 0, [Public Facing Title]@row, PARENT(Test@row)), PARENT(Test@row)))

  • kpierson109
    kpierson109 ✭✭✭
    Options

    Thank you! I think I might have explained what I want wrong. This formula did give the parent row. What if I want it to be the parent of the parent rows? So I want it to populate Module 1 Overview for the rows showing.

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @kpierson109 You should be able to do this by adjusting the formula to be greater than 1.

    Try this...

    =IF(COUNT(ANCESTORS([Public Facing Title]@row)) = 0, " ", IFERROR(IF(COUNT(CHILDREN([Public Facing Title]@row)) > 1, [Public Facing Title]@row, PARENT(Test@row)), PARENT(Test@row)))

  • kpierson109
    kpierson109 ✭✭✭
    Options

    Tried it but still not working.



  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @kpierson109 Let's try this one. This assumes that the Module X Overview Statement will always be the 2nd level of indent.

    =IF(COUNT(ANCESTORS([Public Facing Title]@row)) = 3, PARENT([Public Facing Title]@row), IFERROR(IF(COUNT(CHILDREN([Public Facing Title]@row)) > 1, [Public Facing Title]@row, PARENT(Test@row)), PARENT(Test@row)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!