Linking Parent and Child Rows with a Unique Identifier

Shawn Leahy

Looking for a way to tie Parents to Children with a numeric value, so if they become separated and lost in a sort, they can be easily found and nested under the correct Parent again.

We have an automated [ROW ID] column to reference.

We created a [SLIP ID] column and used =PARENT([ROW ID]@row), and it does populate the correct values for Children, but the Parent rows remain blank.

Is there a way to apply the [ROW ID] to a Parent and all of its Children?

Paul Newcome

I did that backwards. Sorry about that. Change "<>" to "=" (without the quotes).

Paul Newcome

When sorting a sheet with hierarchy, child rows should not be moved out from under their parent rows. They should be sorted with the rest of the child rows within that particular parent, then the parents get sorted amongst themselves.

Have you had an issue with child rows being placed under a different parent when sorting?

Shawn Leahy

Hi Paul,

Thanks for assisting.

Issues typically come when Archiving (moving to another sheet), but can also happen if the person creating the child forgets to indent or removes the indent by mistake and changes the sort (using conditional format has reduced the on-sheet errors)

As long as users are only Archiving Parents, the Children typically go with them but sometimes the Child separated, and we just need a way to identify where it belongs and re-nest it.

I can live with =PARENT([ROW ID]@row) to provide the Parent Row ID in Child rows, but it would be easier if I had (1) column that had the Parent Row ID and the Children that are identified with the same number.

Any thoughts?

Paul Newcome

You can change the PARENT formula to

=IF(COUNT(CHILDREN([ROW ID]@row)) <> 0, PARENT([Row ID]@row), [ROW ID]@row)

Shawn Leahy

Something is not quite right.

It is populating the Parent Row Number in the Slip ID column now, but when a Child is added the Parent Row Number goes away, and the Child Row Number is populated in the Slip ID column.

We are looking for a way for the Slip ID column to have the Parent Row ID number for the Parent and associated Children.

Paul Newcome

Can you provide that same screenshots except with data manually entered to reflect the expected outcome?

Shawn Leahy

Hopefully this makes sense…

Paul Newcome

I did that backwards. Sorry about that. Change "<>" to "=" (without the quotes).

Shawn Leahy

Paul,

Thank you, that worked Perfectly!

Much appreciated!

Paul Newcome

Happy to help. 👍️

If it is any consolation, I do that to myself all the time when pulling parent data to child rows. No idea why I do it, and that's the only time I do. Haha.