How to auto populate ID #s in a sheet

Alex Fullerton

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?

Genevieve P.

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

Shubham

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. 

 

Id # Sequence.png

 I hope this helps you. Have a Good Day. 

Thanks  

Shubham Umale, Smartsheet Engineer, Ignatiuz Software 

Alex Fullerton

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.

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

https://community.smartsheet.com/discussion/comment/352271#Comment_352271

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