Autonumbering of Children Items

Hi,

I have a sheet that has a number of parent items, and each parent has a list of its own children, this sheet also has a Number column. At the moment the use has to fill in the number automatically (i.e. 1,2,3, etc).

I wanted to have a column formula where any task added as a child task for a particular parent task will be numbered at an increment of 1.


Something like:

=IF(HAS([Description]@Row, PARENT()), Parent([No])+1, "")

Issues are:

  1. This is unparseable
  2. That would only work for the first child item, but not for the remaining

Any ideas would help.

Thank you.

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    I've done this before with a lot of helper columns. If somebody has discovered a more efficient method, please do share! Create the columns in the order below:

    1. "AutoNum" = create an # Auto Numbered column
    2. "UniqueID" =IFERROR(Primary@row + AutoNum@row, "")
    3. "ParentID" =IF(COUNT(ANCESTORS()) = 0, "Top", "R" + PARENT(AutoNum@row))
    4. "Child#" =MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, ParentID:ParentID, ParentID@row), 0)

    The "Child#" field will then auto-calculate what number child it is, no matter how many levels of parent/children you could put on the sheet.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!