Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula for Parent, Child, and Nonparent/child rows
I need a formula that encompasses all three of the scenarios listed below. In my case, I have a Project ID systemgenerated column and a Rep_Project_ID column for grouping related tasks. I want parent rows and nonparent/nonchild rows to copy the samerow Project ID and child rows to copy the parent Rep_Project_ID number.
Criteria for the formula:
 1. If there are children (number of children greater than 0), I'm a parent. And I should have a Project_ID, so grab that.
2. If I am a child, grab the Rep_Project_ID from the row above me.
3. If I am not a parent or a child, I should have a Project_ID, so grab that.
I am new to formulas, and what I have so far is returning an error (example for row 6):
=IF(OR(COUNT(CHILDREN([Primary Column]6)) > 0, (COUNT(CHILDREN([Primary Column]6)) <> 0), [Project_ID]6, [Rep_Project_ID]5))
Comments

Hi Lauren,
Provided that you have only a two level hierarchy, the following appears to work as you specified:
=IF(OR(COUNT(CHILDREN([Project ID]1)) > 0, COUNT(ANCESTORS([Project ID]1)) = 0), [Project ID]1, INDEX(ANCESTORS([Project ID]1), 1))
Cheers,
Rob.

Thank you, that worked!!
If we did want to add in a 3level hierarchy, can the formula be modified so that a grandchild cell will still populate with the contents of its first ancestor?

Hi again Lauren,
For an unlimited depth of hierarchy, the following appears to work as you specified:
=IF(COUNT(ANCESTORS([Project ID]1)) = 0, [Project ID]1, INDEX(ANCESTORS([Project ID]1), COUNT(ANCESTORS([Project ID]1))))
This means, "If I don't have any ancestors (and therefore I am at the top of the tree) then return myself, otherwise obtain the list of all of my ancestors and return the last one of that list (who will be the node directly above me)".
Cheers,
Rob

Rob, thank you so much.
The new formula you gave me results in the grandchild row inheriting the value of the child (second ancestor?) instead of the parent, but I would like the grandchild to also inherit the value of the parent/first ancestor.
That being said, I was able to achieve the desired functionality by making a slight modification to your original formula:
IF(OR(COUNT(CHILDREN(Project_ID1)) > 1, COUNT(ANCESTORS(Project_ID1)) = 0), Project_ID1, INDEX(ANCESTORS(Project_ID1), 1))
Now the children and grandchildren share the parent row value. It seems by increasing (COUNT(CHILDREN(Project_ID1)) > 1 to > 2, > 3, etc., this formula holds up regardless of the number of required heirarchies.
Thanks again, your comments were incredibly helpful!

Rob, Lauren,
I am looking to simply display the children of a row (sort of an opposite display of a predecessor). In other words, I want to display what depends on the current row.
Any ideas would be appreciated.
Mark

Hi Mark,
I have done this but it was too computationally expensive for production use.
The technique that I used was to add additional columns:
 "Row number" with a formula "=COUNT([Row Type]$1:[Row Type]18)" for row 18 but copied through all rows. This is the expensive one. It gives the row number of every row.
 "Helper" that extracted the row number of the first predecessor (there could be more than one and you have to throw away the time offsets) of a row. Just a bit of FIND, LEFT, MID, etc as you would in Excel.
 "Name of successor" that searches "Helper" for the "Row Number" and returns the task Name of that row. You can make any number of these columns that would return other attributes of the successor row.
Even though this worked perfectly, it is unimplementable until SmartSheet developers give us a function =Row() that returns the row number of a row.
Cheers,
Rob.