Auto Number: possible to generate Project IDs in Smartsheet?

C. Srinidhi Ragunathan

Hello.

Is it possible to generate Project IDs in Smartsheet ? And can these be done using the Auto-Numbers feature ?

I have a master sheet containing a list of all the projects that have been under taken in a specific year. I need to assign Project IDs for each project. 

Can one generate auto numbers for specific rows ?

As shown in the picture, I want to assign IDs only for projects that are stand alone (see projects A to E) and children tasks (children of Project F and G).

 

Thank you

Nguyễn Hà Anh

So after a Pro Desk session, this is a template for solutions that I got.

For RowID, I use =COUNT($Level$1:$Level@row)

For Level: =COUNT(ANCESTORS()) + 1

Numbering: =IF(Level@row = 0, "", (IF(Level@row = 1, COUNTIF(Level$1:Level@row, 1), "" + PARENT() + "." + COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, >(PARENT(RowID@row))))))

Hope this helps.

Paul Newcome

This cannot be done using the Auto-Numbering system column, but it could be done with a formula.

 

The formula would start in row 2, and it would look something like this...

 

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, COUNTIFS([Number Column Name]$1:[Number Column Name]1, ISNUMBER(@cell)) + 1)

 

[Number Column Name] is whatever column the above formula is in. 

 

If you need row 1 to be a 1, then you would have to manually enter it. Otherwise, if the row doesn't have any children, it will assign a number based on how many rows above it have already been assigned a number.

C. Srinidhi Ragunathan

Thank you. 

One more Question. Is it possible that every sheet can have its own unique ID ?

Thanks

Andrée Starå

Yes, but you would have to add the ID manually. It might also be possible to automate with the help of a third-party tool like Zapier or similar.

Would that work?

Best,

Andrée

Paul Newcome

True, but you could also use a + " - Sheet ID" added on to the end of the formula. If the first ID is going to be in row 1, then you would manually type in 1 - Sheet ID and then starting in row two, the formula would be

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, COUNTIFS([Number Column Name]$1:[Number Column Name]1, ISTEXT(@cell)) + 1) + " - Sheet ID"

 

NOTE: I changed ISNUMBER to ISTEXT.

Adding the ID manually might not be too bad. Since you would have to set up the formula on each sheet anyway

 

If the first row to get an ID is NOT row 1, then you could skip the manual row 1 entry and just put the formula on row 2.

 

And if the Sheet ID is elsewhere on the sheet, it would be 

 

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, COUNTIFS([Number Column Name]$1:[Number Column Name]1, ISTEXT(@cell)) + 1) + " - " + [Column Name]$##

 

Drag-filling/Auto-fill should take care of the rest of it.

C. Srinidhi Ragunathan

Thank you

Paul Newcome

Happy to help! 

Andrée Starå

I'm always happy to help!

Best,

Andrée

jmorales97091

I tried using the above information to set up my sheet. Running into an issue and cannot figure out the solution. 

 

Here's a snapshot of how I have my sheet set up. This sheet is for an Extra Work Authorization Log, the rows under the developer is where I'd like auto numbers to populate as info gets inserted from my form for new entries.

 

This is what I have for my formula but I am receiving the error "Circular Reference"

=IF(COUNT(CHILDREN(DEVELOPER@row)) = 0, COUNTIFS([EWA #]$2:[EWA #]3, ISTEXT(@cell)) + 1)

Paul Newcome

Try adjusting your range...

.

=IF(COUNT(CHILDREN(DEVELOPER@row)) = 0, COUNTIFS([EWA #]$2:[EWA #]2, ISTEXT(@cell)) + 1)

.

The above would go in row 3

jmorales97091

That made progress but when I drag the formula down I just get 1 on everything. 

I will be inputting data periodically from any number developer and/or project so I need it to read through the whole column and find the highest number then add 1 to it.

Paul Newcome

That's because you are using numbers and the formula is looking for text. Try this...

 

=IF(COUNT(CHILDREN(DEVELOPER@row)) = 0, COUNTIFS([EWA #]$2:[EWA #]2, ISNUMBER(@cell)) + 1)

Nguyễn Hà Anh

Hi, I have this workflow and I have tried the formula but it doesn't work.

Please have a look where it went wrong:

Andrée Starå

Hi @Nguyễn Hà Anh

Your attachment wasn’t included. Can you share it again?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Nguyễn Hà Anh

Here it is @Andrée Starå . I want to count level 1 as I, level 2 as I.1, level 3 as I.1.1

I followed the formula but it turned out #circular reference

Nguyễn Hà Anh

@Paul Newcome Do you know about this?