Parent/Child Hierarchy Formulas

Options

Hi,


I have a formula to figure out where I want the parent rows to increase by 1 each time, while the child rows maintain the same number as the parent. IE only the parent rows should increase to match the numbering convention. I'm trying to get the A.X to match what's in the right most column (as those will be deleted and replaced with the formula).


Any help on this type of formula would be appreciated as I have not dealt with Parent/child row syntax before. Thank you!


Tags:

Answers

  • la3815
    la3815 ✭✭
    Options

    Still looking for assistance on this, bumping~ Thanks

  • Khasim
    Khasim ✭✭✭✭✭
    Options

    Hi @la3815

    Please try the below one formula. This will be represented by a number, where "0" represents our main task, "1" represents our Sub Task, and "2" represents our Child Tasks. I hope this helps you.



    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: info@sspmconsultants.com

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • la3815
    la3815 ✭✭
    Options

    Hi, I need the formula to increase by 1 if it is a parent, but I need the child rows to reference the parent.

    IE: Parent row is 5, child rows should say 5 and not 2.

    Thanks,

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @la3815

    There may be a more succinct way to do this, but I would use 3 helper columns to then create the final formula/numbers.

    1 - An Auto-Number column (called Row ID in

    2 - A MATCH formula that turns the Auto-Number column into the Row Number

    3 - A formula to Rank the Auto-Number column if it's a Parent row, or return "Child" if it's a Child row

    And then the fourth:

    4 - Final formula!


    The Row ID column is the Auto-Number, and then the "Row Number" column uses the MATCH function:

    =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    This is helpful because if you change the order of the rows, the Row ID will stay the same but the MATCH function will update to wherever the row is located in the sheet.


    Then the Parent formula is as follows:

    =IF(COUNT(CHILDREN([Primary Column]@row)) > 0, RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Primary Column]:[Primary Column], [Primary Column]@row), 1), "Child")

    It looks to see if the row is a Parent, and if it is, it Ranks the Row Number column. Otherwise it returns "Child".


    Final Formula:

    =IF(Parent@row = "Child", PARENT(Parent@row), Parent@row)

    Let me know if this works for you!

    Cheers,

    Genevieve

  • la3815
    la3815 ✭✭
    Options

    Hi @Genevieve P. this is really close!

    Right now smartsheet is only recognizing rows that have a child as a parent, however, some of my rows do not have children, so I need to smartsheet to recognize that as well.

    IE row 1 might not have any child and is labeled 1

    Row 2 might have 3 children so the parent is labeled 2 and the three tasks are labeled all as 2.

    Any thoughts on how to achieve that or modify the methodology you've presented? I got the formula to work, but need the component i mentioned above, since not every row has a child.

    Thank you

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @la3815

    We can adjust the third column, the "Parent" formula, to be based on an ANCESTORS Function instead of Children (e.g. how many parent rows does the current row have).

    However in order to do this, we would need to know how many levels you have in the sheet and how you want each level organized. For example, I see a green row up at the top as the top level of hierarchy. This would have 0 ancestors. Would this be a different number than the row below, the Grey row, which will have 1 ancestor?

    If you know that the "Parent" rows will always have 2 Ancestors (the Green and Grey rows), then you can adjust your formula like so:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) = 2, RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Primary Column]:[Primary Column], [Primary Column]@row), 1), IF(COUNT(ANCESTORS([Primary Column]@row)) > 2, "Child")

    Then the Green and Grey rows would be blank, without any number.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!