Locating the parent of a parent
I am trying to take the value from the primary column of a level 0 parent and insert it into another column in a level 2 child.
=PARENT([Task Name]@row) works fine when there is only level 0 and level 1.
Something like parent of a parent is what I have imagined would work, but =parent(PARENT([Task Name]@row)) is unparseable.
Ideas?
Best Answers
-
I would suggest three helper columns.
One to output the hierarchy level (Level):
=COUNT(ANCESTORS())
Another to output the Row Number ([Row#]):
=COUNTIFS(Level$1:Level@row, OR(@cell = "", @cell <> ""))
And finally one more to pull the row number of the parent of the parent ([Grandparent Row]):
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row)), Level:Level, 0)
Then we can use this formula to pull from the [Task Name] column based on the number in the [Grandparent Row] column IF the hierarchy of the current row is 2:
=IF(Level@row = 2, INDEX([Task Name]:[Task Name], [Grandparent Row]@row))
-
@MazU We can break the formula ddown into two sections.
The first part:
IF(COUNT(ANCESTORS([Site Name]@row)) > 2
This tells us which rows to put it on. The above says anythign where the count of ancestors is greater than 2. If you just want it on the 3's, you would change the end of that to = 3.
The second part:
INDEX(ANCESTORS([Site Name]@row), 3)
tells us what to pull. This piece you need to remember that we are pulling from an array. Basically a list of the ancestors. The 3 above indicates to pull the third level down which ends up being those rows where you have a 2 in the Ancestors column (remember this starts at 0, so the 3rd entry on our list is 2).
So if you want this to pull the second level or the 1's from the ancestors, change the 3 to 2.
=IF(COUNT(ANCESTORS([Site Name]@row)) = 3, INDEX(ANCESTORS([Site Name]@row), 2))
Answers
-
I would suggest three helper columns.
One to output the hierarchy level (Level):
=COUNT(ANCESTORS())
Another to output the Row Number ([Row#]):
=COUNTIFS(Level$1:Level@row, OR(@cell = "", @cell <> ""))
And finally one more to pull the row number of the parent of the parent ([Grandparent Row]):
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row)), Level:Level, 0)
Then we can use this formula to pull from the [Task Name] column based on the number in the [Grandparent Row] column IF the hierarchy of the current row is 2:
=IF(Level@row = 2, INDEX([Task Name]:[Task Name], [Grandparent Row]@row))
-
@Paul Newcome thanks for the ideas. I didn't fully deploy your pattern, but you provided some very useful concepts which I used:
- counting from top of sheet to identify "grandparent" rows.
- =IF(COUNT(ANCESTORS()) = 0, COUNT([Task Name]$1:[Task Name]@row), 0)
- embedding an INDEX statement inside an IF statement
- =IF(COUNT(ANCESTORS([Task Name]@row)) = 0, "Unit Summary", IF(Rowcount@row <> 0, INDEX([Task Name]:[Task Name], Rowcount@row), INDEX([Task Name]:[Task Name], MAX(Rowcount$1:Rowcount@row))))
- the text I need to find and copy is in a column called Task Name (I insert "Unit Summary" if the row is actually the grandparent - has no ancestors.)
- The column I use to find grandparents is Rowcount
- the results of the search are inserted in a column called Unit (that is the column that holds that long formulae.
In the end, the outstanding challenge is that these formulae cannot be converted into column formulae so there is a bit of end-user risk.
Cheers,
dm
- counting from top of sheet to identify "grandparent" rows.
-
Using the method I outlined above, we can make some tweaks so that everything can be converted to column formulas. The only one in my solution that cannot is the Row # column. To be able to convert this into column formulas, we need to add one more column. An auto-number column. The formatting of the auto-number does not matter. Then the formula to produce the row number would be...
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Then everything can be converted into column formulas.
We can even re-work my final formula to include the "Unit Summary" like so:
=IF(Level@row = 0, "Unit Summary", INDEX([Task Name]:[Task Name], [Grandparent Row]@row))
-
There should be a =TOPPARENT() function. These answers are so obtuse!
-
@Josh Goldberg Feel free to Submit a Product Enhancement Request.
Although... I feel like if we are going to go that route then we could maybe tweak it a little bit. Instead of a TOPPARENT function to pull from level zero, why not a function where we can specify which level to pull from? Maybe a tweak to the existing ANCESTORS function where we can specify the level number to add more flexibility to the function instead of a rigid TOPPARENT type of idea?
-
Totally agree... would like to see ANCESTORS tweaked to allow finding the ancestor at N "level up".
Will submit a request.
-
A few years later, has this been addressed?
@Paul Newcome your initial solution works until I get to "Grandparent Row"- when I get "unparseable" - is this still the cleanest solution for this?
Many thanks!
-
-
The ([Grandparent Row]) formula below is giving be an #unparseable error. Any idea as to why?
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row)), Level:Level, 0)
-
Looks like some misplaced parenthesis.
=MAX(COLLECT([Row#]:[Row#], [Row#]:[Row#], @cell< [Row#]@row, Level:Level, 0))
-
Thanks for responding -- this worked!
Thanks for your help, I'm brand new to Smartsheet.
-
i have my parent levels set up like below
in another column i just want to pull only the 2s from ancestor column
can you please advise
-
@MazU Give something like this a try:
=IF(COUNT(ANCESTORS([Site Name]@row)) > 2, INDEX(ANCESTORS([Site Name]@row), 3))
-
@Paul Newcome sorry Paul, I made an error in my message. I want only the 1s in another column or atleast pull the 1s for against the 3s?
Help Article Resources
Categories
Check out the Formula Handbook template!