Renumbering Children
I am trying to figure out how to set up a column that would set a child's number. If the parent has 5 children then the numbering would be 1-5. If you inserted a child between 2 & 3 then that new record would become 3 and the rest would be 4-6.
I know I have to count the children but I am not sure how to identifies the records position within the order of children.
paul e. reeves
Principal Business Analyst
HMH
Answers
-
You'll need 3 helper columns.
Auto-Number column called "Auto" in this example (formatting doesn't matter).
Text/Number column called "Row" in this example with this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Text/Number column called "Parent" in this row with a formula to pull parent row data onto each of the child rows:
=PARENT([Task Name]@row)
Then in the child numbering column you would use:
=COUNTIFS(Parent:Parent, @cell = Parent@row, Row:Row, @cell<= Row@row)
-
This works nicely for one Parent, but if I add a second family (Parent/Child) the Children numbering is not resetting.
paul e. reeves
Principal Business Analyst
HMH
-
Are you able to post some screenshots with sample data?
-
Paul - I just started over with a fresh worksheet and was able to get it working.
Thank you.
Paul
paul e. reeves
Principal Business Analyst
HMH
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!