11

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.

Andree_Stara

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.

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([email protected])) = 0, COUNTIFS([EWA #]$2:[EWA #]3, ISTEXT(@cell)) + 1)

Try adjusting your range...

.

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

.

The above would go in row 3

In reply to by Paul Newcome

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.

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

 

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