Auto Numbering out of sequence when sections copied

Sherry Fox

Okay, I finally got this working the way I needed. As this project grows I will need to "copy sections" down for additional data. The gray row is for a parent, and the white are the child rows.

The column labeled "Auto" uses the Auto number format. But for the life of me, I do not understand WHY I cannot copy down sections and then the Auto numbering SHOULD update in the correct sequence. It appears the ID & Parent columns are only off because the Auto Number is off. Why is this and how do I fix it?

Hollie Green

It may be the rows that were "pre-added" that you are pasting into. I would try deleting the rows and see if it fixes the issues. Smartsheets always adds some rows automatically at the bottom of every sheet that are blank.

I have seen it be up to 50 rows "pre-added"

Sherry Fox

@Hollie Green

Okay, I deleted the "pre-added rows" like you said to, but I now have no way to get to the next row to paste.

Hollie Green

When you insert a row below you can then paste into that row and it will auto number correctly.

It may add additional rows again later. I would just make sure you are pasting right below your current row if it does add more rows in later so your numbers will continue to increase by 1 without having things out of order.

Sherry Fox

@Hollie Green ,

I tried that like you suggested, and unfortunately, the result was the same. The auto-numbering still continues.

Hollie Green

Delete your Auto Number column save your sheet. Re-add your auto number column and save again. This will sort out the numbers increasing by 1 for each row then moving forward just check where you are pasting so they don't get out of order again. If you are referencing the column from another sheet you may have to re-create your reference.

Paul Newcome

What exactly is your goal for leveraging the auto-number column?

Sherry Fox

@Paul Newcome ,

The ultimate goal is to allow me to have consecutive numbering for rows (in gray), that WILL BECOME parent rows. The

Auto Auto Numbering

Parent =IF(Helper@row = "Parent", Auto@row)

ID =IFERROR(RANKEQ(Parent@row, Parent:Parent, 1), "")

Helper - Last column to the very right, this is a Text field.

Ultimately, I want it to look, just like this. Before I had to type (or paste) the Release # into the Release column in the parent row. That became a pain, and obviously not user friendly. So I decided if I had another sheet (Release List), and it was just a list with 2 columns (ID and Release), I could then do an INDEX/MATCH on this sheet to that one. So everything works perfectly... until I need to copy more formula rows, and then the AutoNumber is off.

Paul Newcome

Leave the auto column alone. Insert a text/number column called "Row" and use this column formula:

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

Then in the Parent column you would use:

=IF(COUNT(CHILDREN([Release Name]@row))> 0, 1)

And in the ID column:

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

Sherry Fox

@Paul Newcome ,

What is the purpose of keeping the AUTO column since you had me add the Row column with a formula? I would have thought that I would replace the Auto Column. All columns are formulas EXCEPT the Helper column. Which is used as part of the formula I created for Parent. My list consist of 19 parent records and their children, plus about 30 "sets" of parent/child placeholders for the upcoming data I am expecting I tried your formulas, and this was my result:

Paul Newcome

As you have seen, the auto-number column does not stay in the order you want it to stay in. The Row column will keep 1 at the top and stay in order regardless of sorting, inserting new rows and deleting old rows.

What do you have in the Report and Release Name columns?

Sherry Fox

@Paul Newcome ,

I undid all of those formulas. Then I left your new Row formula in place. And Kept the Auto, Parent & ID as they previously were. They all work perfectly, even when I do a copy/paste for more sections as needed. I went ahead and hid the Auto, Row & Parent Columns, as the ID column is the only one that is important to be visual for my needs.

As for your question what is in the Report and Release Name columns. The Report column is checked off for each parent column according to the original source data. This is done manually, so I do not have control over the source data that is entered but I must link to it. The Release Name is a Description of the release (parent record), and then the same 5 phases appear as children for each release.

Paul Newcome

Happy to help. 👍️

Glad it is working for you now.