Auto-Numbering in Sequence

Pestomania

So I have a big ask:

• I have a grid "standard number sequencing" which has the following columns:
  • Site Name
  • Department Name
  • Start Sequence Number
  • Number of Procedures Allocated

I have a second grid to determine the list. I have completed this in excel and will paste the formula below, but I want to have Smart Sheet auto-number my documents based on criteria.

Criteria 1: Site Name

Criteria 2: Department Name

Criteria 3: Start Sequence Number

Whenever Site & Department are entered, the system will automatically determine the next number in the sequence defined on "Standard Number Sequencing" grid.

So if I am looking at a document which site and department indicates it should be betwwen 7440 and 7450 but the list already has 7440, 7441, and 7442 allocated, the system will determine 7443 to be the next in sequence.

Is this possible?

<=IFERROR(IF([@[Confirmed?]]<>"",[@[Confirmed?]],"BCP-"&LET(b,XLOOKUP(P2&Q2,SITE_DEPT_NUMBER_LIST[Site Name]&SITE_DEPT_NUMBER_LIST[Type of BCP Document],SITE_DEPT_NUMBER_LIST[Start]),c,XLOOKUP(P2&Q2,SITE_DEPT_NUMBER_LIST[Site Name]&SITE_DEPT_NUMBER_LIST[Type of BCP Document],SITE_DEPT_NUMBER_LIST[End]),IFERROR(IF(ROW()=2,b,IF(AND(ROW()<>2,MAX(--(RIGHT(FILTER(O$1:O1,(P$1:P1=P2)(Q$1:Q1=Q2)),4)))<c),MAX(--(RIGHT(FILTER(O$1:O1,(P$1:P1=P2)(Q$1:Q1=Q2)),4)))+1,"NN")),XLOOKUP(P2&Q2,SITE_DEPT_NUMBER_LIST[Site Name]&SITE_DEPT_NUMBER_LIST[Type of BCP Document],SITE_DEPT_NUMBER_LIST[Start])))),"")/>

Mark.poole

ahh the issue here is that you are starting in this case with a 0 technically. If your starting with 0,10,100, so on anything with a 0. You can adjust the formula by removing the = part of the formula. doing that will start the sequential count with a 0 instead of a 1.

=IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + COUNTIFS([Row ID]:[Row ID], >[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)))

Mark.poole

IT Is Indeed possible though the sequence that you do the countifs is quite different.

IThe The Column$row number: Cell = cell portion from the excel formula would look more like

=countifs(Autonumber:Autonumber,>=Autonumber@row,Range:Range,=Range@row)

With out much more context of what you have in Smartsheet I cant get much more in detail then that.

Pestomania

https://community.smartsheet.com/discussion/comment/440088#Comment_440088

Hi Mark,

So I have this information for test data:

Here is the "BCP Tracker"

I want the second line to automate as BCP-7301. I do not have any auto-numbering columns.

Mark.poole

@Pestomana

You would need an auto number column to make the sequential numbering work. Be it created date column or an actual auto number. Then do the sequential numbering. you could try doing =[Document number]1+1

This how ever can not be a column formula. it will how ever auto generate as you drag it done. If you have an auto sequence number then you could do the following formula. for simplicity sake I am using the column name of the reference sheet for the references.

=BCP+"-"+(Index(Collect({Start Sequence},{Department Name},[Department Name]@row,{Site Name},[Facility Name]@row),1)+Countifs(Auto:Auto,>=Auto@row,[Facility Name]:[Facility Name],=[Facility Name]@row,[Department Name]:[Department Name],=[Department Name]@row))

and if you want to reverse the order of the sequential number then

=BCP+"-"+(Index(Collect({Start Sequence},{Department Name},[Department Name]@row,{Site Name},[Facility Name]@row),1)+Countifs(Auto:Auto <=Auto@row,[Facility Name]:[Facility Name],=[Facility Name]@row,[Department Name]:[Department Name],=[Department Name]@row))

Pestomania

https://community.smartsheet.com/discussion/comment/440099#Comment_440099

So that works amazingly!!!

But I have a few questions:

I added an "if formula" to allow me to stop the change of numbers if a document number has been confirmed:

=IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + COUNTIFS([Row ID]:[Row ID], >=[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)))

It seems to be skipping the first number in the starting sequence:

The number sequences:

Mark.poole

ahh the issue here is that you are starting in this case with a 0 technically. If your starting with 0,10,100, so on anything with a 0. You can adjust the formula by removing the = part of the formula. doing that will start the sequential count with a 0 instead of a 1.

=IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + COUNTIFS([Row ID]:[Row ID], >[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)))

Pestomania

https://community.smartsheet.com/discussion/comment/440110#Comment_440110

Hi Mark,

If I wanted to change this to look at "Site Name" & "Department Name" and instead of going to the "next number", it would add an alpha character and go sequentially.

So if I chose Site "Test" and Department Name "Administrative" it would choose the number 7000 based on the {start sequence} and then all subsequent documents are 7000A, 7000B, 7000C as long as they meet the same details.

So whenever it goes to a different department, it looks at the {start sequence} and identifies the number and goes to the next alpha number?

So first it would be BCP-7000 for "Administrative work" and then BCP-7000A for "Administrative Printing" and BCP-7000B for "Administrative Procurement" and …

Thank you

Mark.poole

Outside of creating a reference table to convert the numeric value to an alphabetical one. I am not really sure how to change the suffix. When ever I run into an issue that I'm not sure out I usually reach out to @Paul Newcome, @Andrée Starå or @Genevieve P.

Pestomania

Hi @Mark.poole, I developed the formula that both works and doesn't work.

This formula (Sequential Numbering Sequence) gives the next number in the sequence (7000, 7001, 7002).

=IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + INDEX(COLLECT({Letter Reference}, {Doc Number}, COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), 1)))

This formula (Sequential Alphanumeric Sequence) gives the next letter in the sequence (7000A, 7000B, 7000C)

=IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + INDEX(COLLECT({Letter Reference}, {Doc Number}, COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), 1)))

But…it fails on 0. I want it to show that if 0, it returns the starting number. I tried to put it together in this nature to say "if 0, look at Sequential Numbering Sequence, otherwise Sequential Alphanumeric Sequence".

=IF(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row) = 0, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + INDEX(COLLECT({Letter Reference}, {Doc Number}, COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), 1)), ""))

Pestomania

https://community.smartsheet.com/discussion/comment/440446#Comment_440446

It took me about an hour of trial and error, I got the formula to work:

=IF(AND(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row) = 0, [Confirmed Document Number]@row = ""), "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), IF([Confirmed Document Number]@row <> "", [Confirmed Document Number]@row, "BCP-" + (INDEX(COLLECT({Start Sequence}, {Department Name}, [Department Name]@row, {Site Name}, [Site Name]@row), 1) + INDEX(COLLECT({Letter Reference}, {Doc Number}, COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Department Name]:[Department Name], =[Department Name]@row)), 1))))