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 Non-parent/child rows

Lauren B
Lauren B ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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. 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 childI 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))




  • Rob Hagan
    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))





  • Lauren B
    Lauren B ✭✭✭✭
    edited 02/08/17

    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
    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)".





  • Lauren B
    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
    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.



  • Rob Hagan
    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.



This discussion has been closed.