Auto-Number conditioned

AP

Hello,

I need to create an auto-number field with these characteristics:

1. Prefix: 2 digit from the alphabet (start with AA).
2. Fill: only 3 digit from 001 to 999.

When I'll reach 1000, how I can increase the second letter of the alphabet of the prefix and restart to 001 (example AB001)?

At same time when I'll reach Z, how I can increase the first letter of the alphabet of the prefix and restart to 001 (example BZ001)?

Thanks and regards.

Darren Mullen

AP,

This can be done. I have done something similar in my own workflow process, so I was able to create a tutorial pretty easily.

Note that having the 3 seed rows at the top of your sheet may or may not be best for your application, so you may want to do this off of your main sheet and pull the ID's in. (This is what I do.. I use a Smarsheet auto-increment column to give each row a unique # and then pull it back into my main sheet).

I hope the video tutorial helps. Formulas are in the description of the video.

https://youtu.be/wPpOWgs2pz8

Toufong Vang

Not to take away from @Darren M's ingenious approach to the puzzle, here's a solution that leverages Smartsheet's auto-numbering. Essentially, the information that you need for creating the ID is contained the automatically generated number. My approach evaluates the number and generates an ID per your specifications.

Below, I detail how to set it up and then provide a summary of the approach.

Setup requires a new sheet that you'll use for referencing the letter needed for the prefix. I named this sheet refAZ. It contains 2 columns. (See screengrab below.)

In the sheet where you need the ID, ensure that you have a column that is auto-numbered. Name this new column autoNUM. You can use a different name but then you'll need to edit the formula.

From this sheet, create a reference with the refAZ sheet as the source. To do this: 

1) Right-click anywhere in the sheet.

2) Select Manage References...

3) Click the +Create button.

4) Hold the shift-key and select both columns from refAZ.

5) Click Insert Reference.

If you don't already have a column for the ID, then create one.

6) Copy-paste the formula below into the ID column.

=IFERROR(VLOOKUP((INT(autoNUM@row / 26000) + 1), {refAZ Range 1}, 2, false) + VLOOKUP(ROUND(((IF(MOD(autoNUM@row, 1000) < 500, autoNUM@row + 500, autoNUM@row)) - (((INT((IF(MOD(autoNUM@row, 1000) < 500, autoNUM@row + 500, autoNUM@row)) / 26000) + 1) - 1) * 26000)) / 1000, 0), {refAZ Range 1}, 2, false) + RIGHT("000" + autoNUM@row, 3), "")

7) Convert this formula into a column formula.

Done!

This formula works because, based on your criteria for incrementation, there are exactly 676,000 possible records that are divided equally among 676 prefixes. (See table below.) And each prefix or can only be assigned to 1000 records. For this reason, it was only a matter of coming up with a way to match the serialized, auto-number to the prefix.

A drawback to using dynamic incrementation is that it depends on existing records/rows. If you're not meticulous about it, moving records or deleting them can cause problems in other rows. In Smartsheet, the auto-number column is not editable. Once created, it is static and does not depend on other records.

Although my solution uses a lookup table, the most important piece of data (auto-number) remains with the record and can be used to recreate the ID in the format that you need.

Darren Mullen

AP,

This can be done. I have done something similar in my own workflow process, so I was able to create a tutorial pretty easily.

Note that having the 3 seed rows at the top of your sheet may or may not be best for your application, so you may want to do this off of your main sheet and pull the ID's in. (This is what I do.. I use a Smarsheet auto-increment column to give each row a unique # and then pull it back into my main sheet).

I hope the video tutorial helps. Formulas are in the description of the video.

https://youtu.be/wPpOWgs2pz8

Toufong Vang

Not to take away from @Darren M's ingenious approach to the puzzle, here's a solution that leverages Smartsheet's auto-numbering. Essentially, the information that you need for creating the ID is contained the automatically generated number. My approach evaluates the number and generates an ID per your specifications.

Below, I detail how to set it up and then provide a summary of the approach.

Setup requires a new sheet that you'll use for referencing the letter needed for the prefix. I named this sheet refAZ. It contains 2 columns. (See screengrab below.)

In the sheet where you need the ID, ensure that you have a column that is auto-numbered. Name this new column autoNUM. You can use a different name but then you'll need to edit the formula.

From this sheet, create a reference with the refAZ sheet as the source. To do this: 

1) Right-click anywhere in the sheet.

2) Select Manage References...

3) Click the +Create button.

4) Hold the shift-key and select both columns from refAZ.

5) Click Insert Reference.

If you don't already have a column for the ID, then create one.

6) Copy-paste the formula below into the ID column.

=IFERROR(VLOOKUP((INT(autoNUM@row / 26000) + 1), {refAZ Range 1}, 2, false) + VLOOKUP(ROUND(((IF(MOD(autoNUM@row, 1000) < 500, autoNUM@row + 500, autoNUM@row)) - (((INT((IF(MOD(autoNUM@row, 1000) < 500, autoNUM@row + 500, autoNUM@row)) / 26000) + 1) - 1) * 26000)) / 1000, 0), {refAZ Range 1}, 2, false) + RIGHT("000" + autoNUM@row, 3), "")

7) Convert this formula into a column formula.

Done!

This formula works because, based on your criteria for incrementation, there are exactly 676,000 possible records that are divided equally among 676 prefixes. (See table below.) And each prefix or can only be assigned to 1000 records. For this reason, it was only a matter of coming up with a way to match the serialized, auto-number to the prefix.

A drawback to using dynamic incrementation is that it depends on existing records/rows. If you're not meticulous about it, moving records or deleting them can cause problems in other rows. In Smartsheet, the auto-number column is not editable. Once created, it is static and does not depend on other records.

Although my solution uses a lookup table, the most important piece of data (auto-number) remains with the record and can be used to recreate the ID in the format that you need.

Amanda Fulbright

@Toufong Vang I'm looking for a similar solution to what you proposed here and wondered if you might be able to help me with it. I have a sheet to track IT Decisions where there is a parent row for each decision and then child rows underneath for each committee that needs to approve the decision. I want each parent row to have a sequential ID number (EHR1, EHR2, EHR3...) and each child row to use that ID and append a number to it (EHR1.1, EHR1.2, EHR1.3). I used the WBS solution on this forum to get this to work. However because each child row is getting an auto number as well my primary decisions are skipping a lot of numbers. I think I need to use a helper sheet to generate the unique ID for the parent row and then reference that ID to make the child rows but I can't figure it out.

I have another thread going about this with my formulas and screen shots. Would you mind taking a look?

https://community.smartsheet.com/discussion/comment/335284#Comment_335284

Toufong Vang

Hi, @Amanda Fulbright

Here's how I approached it...

I. Create an auto-number column and ensure that it contains only numbers. The autonumber is going to be used as a "serial" number for the row. This helps to determine which row(s) came before any row.

II. Create the following helper columns in the following order.

• Parent_Serial (finds the parent of a row if there is one and returns the parent's serial number)

=IF(PARENT(AutoNum@row) <> 0, PARENT(AutoNum@row), 0)

• Parents_Count (counts the number of parents that were created before the current one)

=IF([Parent_Serial]@row = 0, COUNTIFS([Parent_Serial]:[Parent_Serial], @cell = 0, AutoNum:AutoNum, @cell < AutoNum@row) + 1, "")

• Child_Count (counts the children that were created before the current one)

=IF([Parent_Serial]@row <> 0, COUNTIFS([Parent_Serial]:[Parent_Serial], @cell = [Parent_Serial]@row, AutoNum:AutoNum, @cell < AutoNum@row) + 1, "")

• ID_Parent (creates the ID if the current row is a parent)

=IF([Parent_Serial]@row = 0, "EHR" + [Parents_Count]@row, "")

• ID_Child (creates the ID if the current row is a child)

=IF([Parent_Serial]@row <> 0, INDEX([ID_Parent]:[ID_Parent], MATCH(PARENT(AutoNum@row), AutoNum:AutoNum, 0)) + "." + [Child_Count]@row, "")

• Parent_ID (returns the ID of the parent row for the current child row)

=INDEX([ID_Parent]:[ID_Parent], MATCH(PARENT(AutoNum@row), AutoNum:AutoNum, 0))

III. Create your ID column. (I'm assuming you want this to be the sheet's primary column)

• ID

=IF([ID_Parent]@row <> "", [ID_Parent]@row, [ID_Child]@row)

This approach assumes that:

• All new rows are parent rows. When a row is moved under a parent, the helper columns will change accordingly and the row's ID will also change. (see EHR1.1)
• All child rows added to or moved into a parent row will be assigned the sequential child ID based on the order of its serial number. (see EHR4.2)

ID's are dynamic so when a child row or a parent row is deleted, the next sequential child/parent row assumes the ID of the prior row.

This was a fun puzzle. 😀

Cheers!

Amanda Fulbright

@Toufong Vang Awesome solution! Thanks for your help!

Melissa Torrez

@Toufong Vang I am using what you shared above in a similar approach, however I would like to structure it like below. I also need a way to add a 3rd decimal point and 4th character such as 1.A.1.a when there is a sub-task of a child row that could get inserted after the initial setup shown in the example below. Is that possible?

Melissa Torrez

https://community.smartsheet.com/discussion/comment/335683#Comment_335683

How can you append if there is a 3rd level to the WBS. Currently, the formulas for this approach don't take into consideration a 3rd level where you might have in my scenario:

WS1.1 (Parent row)

WS1.1.1 (child row of WS1.1)

WS1.1.2 (child row of WS1.1)

WS1.1.2.1 (child row of WS1.1.2) - in the ID column I am struggling to update the formula to display the outcome as WS1.1.2.1. For reference the existing ID column formula is: =IF([ID_Parent]@row <> "", [ID_Parent]@row, [ID_Child]@row)

Janna

Hi,

I'm not sure if it's the right topic. But I am looking for a formula to automatically populate the Action Number field in the primary row.

I want to automatically fill the row according to the number of actions of each department. So if the Sales department is selected and it is the first action, it will be named Sales 1. If they are entering their second action, it will be named Sales 2, if Distribution enters an action it will be named Distribution 1 etc. 

So far I have this part of a formula but I am missing the part where the actions of each Department will be counted: ="Aktion" + "-" + Department@row

I hope you understand, what I mean, can you help?

Thanks a lot in advance.