How do I populate the highest parent level data into another cell?

Shane Sorensen

I'm using the '=PARENT([Primary Column]@row)' formula, but it's populating data from the parent row, even if the parent row is a child of another parent. I need to populate in a separate cell data from the highest level parent row.

In the example below, is there a formula that would populate Primary Column row 1 data, "Main Parent 1" into Column2 rows 2-7, so that "Main Parent 1" is populated in rows 2-7? Then, I could use that same formula to populate "Main Parent 2" in rows 9-14.

Thank you!

James Keuning

I do it like this:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Primary Column]@row, INDEX(ANCESTORS([Primary Column]@row), 1))

Genevieve P.

Hi @Shane Sorensen

There may be a more succinct way to do this, but what I would do is keep your current Column2 as a helper column to pull the immediate Parent. Then you can set up your final formula column to pull the "grandparent" row by checking the hierarchy level with the ANCESTORS function.

If the COUNT of the ANCESTORS for the current row is 2 (meaning it's a second-level child row), then return the PARENT of the helper column you set up, or in your instance Column2. However if it's not a second level, return the exact same cell as in your helper column.

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 2, PARENT([Parent Helper]@row), [Parent Helper]@row)

You can then hide the helper column so it's not visible in your sheet. Let me know if this works for you!

Cheers,

Genevieve

Shane Sorensen

Thanks @Genevieve P. I think it's almost there, however, if I go deeper levels on the children, the formula is pulling the parent still for some, and not the grandparent. I dragged down the formula to the top, and as you can see, 'Bushes' and 'Clown' show up instead of 'Books'.

Link to public sheet: https://app.smartsheet.com/b/publish?EQBCT=ada66de0e6714c8b8ad415004a45f17a

James Keuning

I do it like this:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Primary Column]@row, INDEX(ANCESTORS([Primary Column]@row), 1))

Jim B

Thank you for this @James Keuning.

DeyeaM

Hi All!

In project management, I've often found that we need to pull data based on several factors and so to aid in this, I generally will create a column with drop-down values (single selection) that I use to help define the row and drive things like conditional formatting to help differentiate row types. This is also helpful in creating what I personally call "tag formulas", or formulas that help to assign tags to rows so I can group them and reference them cleanly in filters, reports, and dashboards. In the example below, I use the "Level" column as the helper column to drive the two formulas I use for the Phase and Project tag formulas. I personally find this to be easier to write/edit and troubleshoot than the ancestors approach.

To pull the Project Name for each of the child/grandchild rows, I use the following formula:

• =IF(Level@row = "Project", [Task Description]@row, PARENT())

To pull the Phase for each of the child/grandchild rows, I use the following formula:

• =IF(Level@row = "Phase", [Task Description]@row, PARENT())

These formulas help to pull the cell data for the item you wish to use as the "tag" into the cell you wish to serve as the primary parent and then cascades it down to the rows that fold up to it.

I hope this helps someone!

- D