Sequential Number

I am creating a Project Numbering system in a sheet that are generated based on selected criteria's. The last 3 digits would need to be sequential numbers "000"

Auto-numbering will not work for this application as the criteria's differ for each program.

Follow is what the formula looks like in excel:

&TEXT(COUNTIF($B$2:B3,B4)+1,"000")

An example of how the number looks at the end is:


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something along the lines of

    ="C539-50-G" + IF(COUNTIFS($B$2:B3, B4) < 10, "00", IF(COUNTIFS($B$2:B3, B4) < 100, "0")) + COUNTIFS($B$2:B3, B4)

  • KempenUSA
    KempenUSA ✭✭✭

    great Thx, will try this.

  • APalani
    APalani ✭✭

    Hi Paul,

    I have similar request to create a auto number, sequence is

    "ECR- 2021-8-0001": ECR- Year- Month- 0000

    I am able to pull the month from the date it the log was created, but not able to update the last batch.

    If its September: ECR-2021-09-0001

    I use "ECR-2021" + "-" + MONTH(Created1) formula , but need to input sequence numbering

    eg: ECR-2021-8-0001

    ECR-2021-8-0002

    ECR-2021-9-0001

    ECR-2021-9-0002

    Can you suggest better way to fill it up?

  • APalani
    APalani ✭✭

    This is what i tried but keep getting same value for the month. Is there any better solution?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @APalani Try something like this...


    ="ECR-2021" + "-" + MONTH(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

  • APalani
    APalani ✭✭

    Paul, thanks alot , it works really good. I tried on a log with same day/time, it generated different ECR number.

    But I was asked to include the date as well. I want to know if its feasible.

    ECR-Year-Month-Date00

    In above format Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To include the day it would be this:

    ="ECR-2021" + "-" + MONTH(Created@row) + "" + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 10, "000", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 100, "00", IF(COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row))) < 1000, "0"))) + COUNTIFS(Created:Created, AND(@cell<= Created@row, IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row)))

  • APalani
    APalani ✭✭

    Thank you so much Paul, the function works well.

  • APalani
    APalani ✭✭

    Paul, one request: is it possible to increment based on the date

    For eg:

    ECR-2021-08-23-0001

    ECR-2021-08-24-0001

    ECR-2021-08-24-0002

    something like this.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. This should work for you...


    ="ECR-" + YEAR(Created@row) + "-" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "-" + IF(DAY(Created@row) < 10, "0") + DAY(Created@row) + "-" + IF(COUNTIFS(Created:Created, @cell = Created@row) < 10, "000", IF(COUNTIFS(Created:Created, @cell = Created@row) < 100, "00", IF(COUNTIFS(Created:Created, @cell = Created@row) < 1000, "0"))) + COUNTIFS(Created:Created, @cell = Created@row)

  • APalani
    APalani ✭✭

    Hi Paul,

    The last number is repetitive.



  • APalani
    APalani ✭✭

    Hi Paul,


    I used the previous functions with month->day and year->month. Looks like its working.


    ="ECR-2021" + "-" + MONTH([Created On]@row) + "-" + DAY([Created On]@row) + "-" + IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 10, "000", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 100, "00", IF(COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row))) < 1000, "0"))) + COUNTIFS([Created On]:[Created On], AND(@cell <= [Created On]@row, IFERROR(DAY(@cell), 0) = DAY([Created On]@row), IFERROR(MONTH(@cell), 0) = MONTH([Created On]@row)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I had initially started down that route but wanted to try to simplify it since we are now looking at the full date instead of just month and year.

    But the more I look at the "simplified version" the more I realize it was flawed in a few different ways.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!