7

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

Functionality

Comments

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.

In reply to by C. Srinidhi Ra…

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

In reply to by Andrée Starå

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.