Formula to return Parent name of child?

Hoon Kim

I'm trying to figure out how to return the name of a child's Parent. 

 

Example:

Parent 1

• Child 1
• Child 2
• Child 3

Parent 2

• Child 4
• Child 5

 

In the above, I would love to have a column on Row Child 1 that automatically returns "Parent 1" and for Child 4 returns "Parent 2". Is there a way to have a formula function that returns this fuction? 

Richard Rymill SBP

Yes there is and one of my colleagues, JameR or Craig can tell you how they did it, but bear with them are are both tied up right now.  We have done this for our customers.

 

RichardR Smarterbiz

J. Craig Williams

I THOUGHT my ears were burning. Now I know.

 

It is pretty easy.

 

 

For my example, the Project_ID is filled in (every project has its own ID)

In the Rep_Project_ID, here's the formula for row 23

 

=IF(COUNT(CHILDREN([Primary Column]23)) > 0, Project_ID23, Rep_Project_ID22)

 

If there are children (number of children greater than 0), I'm a parent.

And I should have a Project_ID, so grab that.

If I'm not a parent, grab the Rep_Project_ID from the row above me.

 

Does NOT work for sub-projects (but can be done).

May NOT work if you like to skip rows. (don't do that)

 

Hope this helps.

Craig

 

 

 

Richard Rymill SBP

Thanks Craig, well explained. 

RichardR

J. Craig Williams

De nada.

 

Craig

Hoon Kim

Thanks so much for this.

Lauren B

In reference to the example Craig posted, what if you have a row that is not a parent or a child? It is a task that stands on its own that will not have any sub-tasks. 

 

So building off of Craig's original example, I need a formula that encompasses all three of the following scenarios. In my case, the Project ID field is system-generated, and I want parent rows and non-parent/non-child rows to copy that number and child rows to copy the parent number.

 

1. 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.

 

J. Craig Williams

Lauren,

 

You'll need to add in ANCESTORS()

This returns 0 when there are no ancestors (parents, grand-parents, great-grand-parents, etc...)

 

=IF(OR(COUNT(ANCESTORS(Example23)) = 0, COUNT(CHILDREN(Example23)) > 0), "gp,parent,childless", "child")

 

Replace the text with your project ID's.

The formula is true when the row has children or is at the top of their ancestor chain.

 

Hope that makes sense.

 

Craig

 

Lauren B

Craig - thank you so much for the response. It looks like the formula you provided is populating all gp, parent, childless, and child rows with the same value, respectively. I need children and grandchilden to inherit the same value as their actual parent.

 

I was actually able to achieve the desired results with a modification to a formula from Rob Hagan:

 

IF(OR(COUNT(CHILDREN(Project_ID1)) > 2, 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)) > 2 to > 3, > 4, etc., this formula holds up regardless of the number of required heirarchies. 

 

 

Thank you again for your quick response!

Bill Brandt

I would like to do the above but not have to enter the specific cell reference. The sheet already exists so I would like to be able to paste a generic formula. I need to fill in the grand parent cell with the parent text + the child text. Working with the formulas above I can't seem to get it to work without specifically creating cell references.

Thanks

 

Bill