Formula Help with Parent Function

kpierson109

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.

JamesB

@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

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.

kpierson109

JamesB

@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

Tried it but still not working.

JamesB

@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)))