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

D Gray

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

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 -

PARENT([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!