How to display the grandparent task name or value in a cell

Sweet

Hello,

I would like to display the name or value in a cell, please. I could get the parent by:

IF(ISNUMBER([Stages Tasks]6), PARENT([Stages Tasks]6), "")

but I would like to get the Grandarent instead.

Thx for your help.

Find more posts tagged with

Comments

Matt Stofka

Hi Sweet,

You can use the ANCESTORS function with INDEX to grab the value in a grandparent cell in the same column as the child cell:

INDEX(ANCESTORS(<child_cell>), <parent_index>)

e.g.; INDEX(ANCESTORS([Stages Tasks]6), 2)
There's more info on the ancestors and index functions here: https://help.smartsheet.com/articles/775363-using-formulas

...and if you search those terms in Community you'll find several helpful posts showing examples of how to use these.

Matt

ewrobbins

I'm struggling to get this formula to work. How do you identify the <parent index> in your description above? I simply want to pull in the text field from the highest level ancestor - in some cases this is the parent, sometimes it's the grandparent, sometimes it's the great-grandparent.

Shaine Greenwood

Hello,

You can use an index of 1 to identify the parent-most cell:

=INDEX(ANCESTORS(), 1)

If you're wanting to identify the grandparent of one cell from another, reference that adjacent child cell: