Auto-Numbering in Sequence

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])))),"")/>

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/10/24 Answer ✓

    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)))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Pestomania
    Pestomania ✭✭✭✭✭

    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
    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))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Pestomania
    Pestomania ✭✭✭✭✭

    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
    Mark.poole ✭✭✭✭✭✭
    edited 06/10/24 Answer ✓

    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)))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Pestomania
    Pestomania ✭✭✭✭✭

    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
    Mark.poole ✭✭✭✭✭✭
    edited 06/12/24

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Pestomania
    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
    Pestomania ✭✭✭✭✭

    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))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!