How to auto populate ID #s in a sheet

Options
Alex Fullerton
edited 11/29/22 in Formulas and Functions

I would like to set up a sheet with projects and corresponding ID #s. If a project that already exists in the sheet is entered, I want that row to use the same ID # as the existing project's, otherwise add 1 to the largest existing ID # to create a new ID #. Any ideas on how could achieve this with formulas and helper columns/sheets?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Alex Fullerton

    What I would do here is have a second, reference sheet where all of the unique project names are stored, with their associated ID Number.


    For example, in my source sheet I would set up two helper columns:

    • A System Date Created column
    • A column to identify if the row is the first instance of this project or not, with a formula

    I use the Created date column to identify if the current row has the oldest date (or the MIN date). If it does, I return "First Entry". If there's a different row on the sheet with an older date, I return "Duplicate".

    =IF(Created@row = MIN(COLLECT(Created:Created, [Project Name]:[Project Name], [Project Name]@row)), "First Entry", "Duplicate")


    This enables me to set up a Copy Row Workflow to copy rows to my Reference sheet, but ONLY if they're the very first entry of this specific Project Name.


    In my reference sheet, I have an AutoNumber column set up, with my preferences for how I want the unique IDs to be created:

    Notice that ALL the columns are brought in from the source sheet with the Copy Row workflow... but I don't need anything else, other than the AutoID. If you want, you could hide the other columns.


    Now that we've generated a unique ID for the first instance of this value, we can use an INDEX(MATCH formula in the first sheet to bring in that AutoID from the reference sheet, based on the Project Name:

    =IFERROR(INDEX({AutoID}, MATCH([Project Name]@row, {Project Name Column}, 0)), "")


    And there we have it! 🙂 Let me know if this works for you.

    Cheers,

    Genevieve

Answers

  • Shubham
    Shubham ✭✭✭✭
    edited 11/22/22
    Options

    Hi Alex Fullerton 

    I hope you're doing well. 

    Please select column type “Auto Number” then according to your requirements you can select option. 

    Ex. Suppose Id # start from 1001 so please select Prefix 1, Numerical places 000 and Starting number 1. 

     


     I hope this helps you. Have a Good Day. 

    Thanks  

    Shubham Umale, Smartsheet Engineer, Ignatiuz Software 

  • Alex Fullerton
    Options

    Hi Shubham,


    Unfortunately, this does not help. I need new entries that match the names of old entries to have the same ID #.

    Example:

    ID # Proejct Name

    4 Lambda

    2 Beta

    3 Gamma

    2 Beta

    1 Alpha

    Any idea how to have these ID numbers populate automatically based on Project Name?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Alex Fullerton

    What I would do here is have a second, reference sheet where all of the unique project names are stored, with their associated ID Number.


    For example, in my source sheet I would set up two helper columns:

    • A System Date Created column
    • A column to identify if the row is the first instance of this project or not, with a formula

    I use the Created date column to identify if the current row has the oldest date (or the MIN date). If it does, I return "First Entry". If there's a different row on the sheet with an older date, I return "Duplicate".

    =IF(Created@row = MIN(COLLECT(Created:Created, [Project Name]:[Project Name], [Project Name]@row)), "First Entry", "Duplicate")


    This enables me to set up a Copy Row Workflow to copy rows to my Reference sheet, but ONLY if they're the very first entry of this specific Project Name.


    In my reference sheet, I have an AutoNumber column set up, with my preferences for how I want the unique IDs to be created:

    Notice that ALL the columns are brought in from the source sheet with the Copy Row workflow... but I don't need anything else, other than the AutoID. If you want, you could hide the other columns.


    Now that we've generated a unique ID for the first instance of this value, we can use an INDEX(MATCH formula in the first sheet to bring in that AutoID from the reference sheet, based on the Project Name:

    =IFERROR(INDEX({AutoID}, MATCH([Project Name]@row, {Project Name Column}, 0)), "")


    And there we have it! 🙂 Let me know if this works for you.

    Cheers,

    Genevieve

  • Alex Fullerton
    Options

    Ou, referencing auto ID's in a helper sheet - very clever! I will give that a try, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!