Is it possible to update an auto-number field that was incorrectly input the first time?

Shelby
Shelby ✭✭✭✭✭
edited 03/16/21 in Formulas and Functions

Hi All! A request came in to modify an existing auto-number field that was input incorrectly the first time. This wasn't a Smartsheet error, it was a user error. The creator of the sheet did not have the auto-numbering system set up to include the current year {YY}. I've corrected that issue but now I need to correct the old rows that were auto-numbered with the year they were created.

Suggestions?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Shelby

    There are a few ways you can do this.

    Depending on how your numbering is set up, you could just delete the current Auto-Number column and then re-insert the column with the correct set up. However this will re-number your rows, so if there are some rows that had been moved or deleted your numbering will change. (See the "Notes" at the bottom of the top section of this Help Article: Auto-Number Rows).

    Another way to do this would be to change the current Auto-Number column into a regular text column. Then adjust the rows that need the {yy} addition to have the correct data. Once the values are correct, change the Column Properties back to being an Auto-Number column.

    Keep in mind with this option you'll want to ensure you have the right "Starting Number" set in your Column Properties so it starts off where the data currently ends. (See: Turn an Existing Column into an Auto-Number Column).

    Let me know if either of these work for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Shelby

    There are a few ways you can do this.

    Depending on how your numbering is set up, you could just delete the current Auto-Number column and then re-insert the column with the correct set up. However this will re-number your rows, so if there are some rows that had been moved or deleted your numbering will change. (See the "Notes" at the bottom of the top section of this Help Article: Auto-Number Rows).

    Another way to do this would be to change the current Auto-Number column into a regular text column. Then adjust the rows that need the {yy} addition to have the correct data. Once the values are correct, change the Column Properties back to being an Auto-Number column.

    Keep in mind with this option you'll want to ensure you have the right "Starting Number" set in your Column Properties so it starts off where the data currently ends. (See: Turn an Existing Column into an Auto-Number Column).

    Let me know if either of these work for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!