Returning a Value When an Item is a Child of a Parent That Has a Specific Name

ahelleny

I need to create a formula that looks at all children (of varying levels) within a parent, and returns a specified name in a column for ALL children rows when the level 1 parent value is X.

Jeff Reisman

@ahelleny

By "returns a specified name in a column" do you mean that it sets a cell value in the column for all child rows with a parent row value of X?

Can you add a screenshot of your row hierarchy structure?

ahelleny

What I'm trying to achieve is a sort of IF statement based on the parent/ancestral value for the cell. Something like, IF the highest level parent value = "Summary", THEN display "X" in the cell. I'm trying to do this to identify the summary section of each sheet (there are many of these sheets), so I can then use the value in the field as an identifier/filter in a report.

Brent Wilson

What about

=IF(COUNT(ANCESTORS(Description@row))=0, "X")

This would get you the top level

If you have things like Headers such as "Quarter" and indent the records under that you will have to change the IF to a higher number..

Something like

=IF(COUNT(ANCESTORS(Description@row))=1, "X")

You might also want to change the column to a check box field and rewrite the statement as

=IF(COUNT(ANCESTORS(Description@row))=0, 1)

then it will give you a fancy little check box on those top values

ahelleny

This got me part of the way there, but still trying to work through it to get the necessary end result. I also referenced this other discussion, but still haven't fully achieved my end goal. https://community.smartsheet.com/discussion/74500/locating-the-parent-of-a-parent?irclickid=RwqwRwwQ3zQSWNsx%3A0Rqe1CuUkDwO80hy13n280&irgwc=1&Impact_source=affiliate&impact_partner=adgoal%20GmbH

Here is the current state of the sheet and the formulae I used to get there:

Level: =COUNT(ANCESTORS(ID@row))

Row#: =MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)

Grandparent Row: =IF(Level@row = "1", PARENT(ID@row), (IF(Level@row = "2", PARENT(ID@row), IF(Level@row = "3", PARENT(ID@row)))))

My goal is to identify all rows within a hierarchy with a value I can use to filter a report to only show rows that are part of the Summary section of the sheet.