Parent/Child Hierarchy Formulas

la3815

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!

la3815

Still looking for assistance on this, bumping~ Thanks

Khasim

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.

la3815

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.

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

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.

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.