Auto Numbering out of sequence when sections copied

Options

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?


Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/16/23
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    edited 06/20/23
    Options

    @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.

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/20/23
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    Options

    @Hollie Green ,

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


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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.


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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:


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Sherry Fox ✭✭✭✭✭✭
    edited 06/21/23
    Options

    @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.


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


    Glad it is working for you now.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!