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 to return Parent name of child?
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?
Comments
-
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
-
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 -
Thanks Craig, well explained.
RichardR
-
De nada.
Craig
-
Thanks so much for this.
-
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. 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.
-
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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives