Can you create a column formula for auto-number generation that will recognize ancestor relationship

I have a sheet that I would like to custom create an auto-number formula so a record ID is given when a new record is submitted, but I also also want it to be able to recognize descendant rows and give them the same Record ID as the ancestor row (instead of generating a new record ID for every new row). Below is an example of the result I'm aiming for.



  • Coop22
    Coop22 ✭✭✭✭

    Hey @D Gray!

    It's sounding like you will need a couple of helper columns that you will need to hide. I tend to set these to the left of the Primary column so that they are a bit less intrusive with the, for a lack of better words, "Hidden Column Indicator," but that is purely preference!

    I accomplished what you were looking for by using 4 helper columns with column formulas.

    Hier - This column tells you the level in the hierarchy. The formula is:

    =IF(PARENT(Hier@row) = "", 1, PARENT(Hier@row) + 1)

    Row ID - This is just the autonumber column. Don't mistake this for a good Row ID column as it is generated as rows are added and do not change if the row is moved. i.e. Row ID in row 6 is 6 then is moved to row 7, Row ID will still be 6.

    Row_ID - This column will correctly number the row. The formula is:

    =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    Number - This column will number your projects as well as the tasks underneath them. The formula is:

    =IFERROR(RANKEQ([Row_ID]@row, COLLECT([Row_ID]:[Row_ID], Hier:Hier, 1), 1), [Row_ID]@row -


    Once you have those set up, you can create your Record ID by using this formula:

    =IF(Hier@row = 1, "Record ID - " + Number@row, PARENT([Record ID]@row))

    If you want to also automate the "Project/Task" Primary column, you can use this formula:

    =IF(Hier@row = 1, "Project " + Number@row, "Task " + Number@row)

    That is if you're willing to forego the Project A/B for Project 1/2.

    Let me know if this works out for you and you can of course change the column names to whatever you'd like!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!