Formula for Parent, Child, and Non-parent/child rows

I need a formula that encompasses all three of the scenarios listed below. In my case, I have a Project ID system-generated column and a Rep_Project_ID column for grouping related tasks. I want parent rows and non-parent/non-child rows to copy the same-row 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))

Find more posts tagged with

Sheets

Formulas

Legal

Solution Center

Community Platform

Conditional Formatting

Project Management

Cell linking

Comments

Rob Hagan

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.

Lauren B

Thank you, that worked!!

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

Rob Hagan

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

Lauren B

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!

Screen Shot 2017-02-08 at 10.55.07 PM.png

MarkR

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

Rob Hagan

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.