Sequential numbering of Child Rows that restart with each parent row

I have a sheet with a parent row (PO number) and multiple child rows (parts ordered per PO number) that I am trying to auto-assign a number starting with 1, to the child rows of that parent only so I can use it to populate another sheet based on PO number AND that auto number. Is there a formula that can do that?

I've created a helper column [Item Number] that assigns a "1" to child rows and a "0" to the parent row. Then I have a second column [Item #] with the formula =SUM(PARENT([Item Number]@row):[Item Number]@row)) to assign 1,2,3,4,5,6... but I get an #UNPARSABLE error. If I use the formula =SUM($[Item Number]$1:[Item Number]@row)) IT works but doesn't start over after the next parent row. What am I missing?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Insert an auto-number column (called "Auto" in this example). Formatting doesn't matter. Then you will need a text/number column (called "Row" in this example) with the following column formula:

    =MATCH(Auto@row, Auto:Auto, 0)


    Next you will need another helper column (called "Parent" in this example) with the following column formula:

    =PARENT([Primary Column Name]@row)


    Then to get your sequential numbering in a text/number column you would use:

    =COUNTIFS(Parent:Parent, @cell = Parent@row, Row:Row, @cell<= Row@row)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!