Adjust the order of the rows when a new row is inserted

Smtshtuser

I have a sheet which contains a list of flow steps. I have numbered them in a particular manner (1,2,3,4,5,6....). If I want to add a step somewhere in between I want the number of all subsequent steps to adjust to the next number.

This order is important and needs to be retained.

I do not want to use Auto numbering, rather I want to dictate the sequence myself.

I would do such a thing in excel by using the Row() function. Is there anything similar I can do here. I would prefer to use formulae so that I can convert it to a column formula.

I can use this column for sorting in reports to retain the order of steps

Paul Newcome

Insert an auto-number column (called "Auto") then insert a text/number column with the following column formula:

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

Natalia O'Grady

I used the Prefix-Suffix-ParentID-RowID-Level-isParent combination provided in this forum to achieve this result with children rows. I am now after the opposite effect, I would like to add a row in between sequential children rows and have it marked as the last digit in the sequence. For example:

Parent Row

1.1 Existing child row

1.3 New child row

1.2 Existing child row

The idea is that the existing children rows do not change their original number.

Is this possible?

Paul Newcome

@Natalia O'Grady Can you provide a link or a copy of each formula? You should be able to reference the auto-number column instead of the row number column (if I am thinking of the right solution that you're using).

Natalia O'Grady

Hi Paul. Yes, I actually figured that out and it worked. Thanks for getting back to me. I now have an issue when the rows are deleted or moved out, the numbering changes (which is fair enough, because the number of rows changed). I need all the numbers remain unchanged when the rows are deleted or moved out. Is this possible?

Paul Newcome

You would need to set up a type of "intake sheet" where you have all of the existing rows and their numbers calculated there. You would never delete a row from this sheet. You would have a copy row automation set up to pull a row from this sheet to the working sheet whenever the WBS is populated. You can add new rows here and the copy row automation will push the row over to your working sheet with the WBS captured as static data.