Auto Number to Reset Daily (PO #)

MCorbin
MCorbin Overachievers Alumni

I'm trying to create a PO #. They want to include the date and a sequential number in the PO #, e.g.

2020031100005

2020031100006

I can use the "Auto-Number" function, BUT... they want the number to reset back to 1 for the next date... which auto-number doesn't do.

Is there a way to do a running count that would basically add a number for each row added on a given day?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)


    Should not give a sequential date but a sequential number based on the date.


    In all reality... Any formula can cause performance issues on larger sheets. A lot of it depends on what else you also have going on. I have one sheet that is only about 700 rows long. There are a handful of basic formulas in every row (to include a variation of the above). That doesn't slow down too much. It's when I add in the additional 20x23 section of wicked complex formulas that it runs REALLY slow. It can take upwards of 10-15 minutes for the sheet to process something as simple as the addition of a finish date. Ugh! I too have reached out to Smartsheet and gotten the same reply that they are working on the performance issues. Hopefully they are able to upgrade sooner rather than later.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    As a possible workaround, the third-party service, Zapier, could be an excellent option for this scenario.

    Is that an option for you?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • MCorbin
    MCorbin Overachievers Alumni

    Unfortunately, Zapier isn't an option for us at this time. I did find a way to make it work - it's not ideal, but it works.

    I have a header row where this column ("Reset PO") = 0

    =IF([PO Date]@row = [PO Date]5, [reset PO]5 + 1, 1)

    It's looking at the PO Date in the previous row. If it's the same date as the current row, it adds 1 to the the Reset PO # from the previous row. If the current row is a different date, it starts with 1.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/12/20

    There is another option...


    =COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

    This will give you the sequence for the count for the date.


    =YEAR([PO Date]@row) + "" + MONTH([PO Date]@row) + "" + DAY([PO Date]@row)

    This will output the date as yyyymmdd


    Now we use an IF statement to insert the leading zeros before the COUNTIFS to make then all the same length (00001 - 99999 kind of thing).

    =IF(COUNTIFS(.............) < 10, "0000", IF(COUNTIFS(............) < 100, "000", IF(COUNTIFS(..........) < 1000, "00", IF(COUNTIFS(............) < 10000, "0")))) + COUNTIFS(............)


    We drop the COUNTIFS in the above to give your zero filled count:

    =IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10, "0000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 100, "000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 1000, "00", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10000, "0")))) + COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)


    Finally we drop the date portion of the sequence into the beginning, and you should have the numbers you are looking for...

    =YEAR([PO Date]@row) + "" + MONTH([PO Date]@row) + "" + DAY([PO Date]@row) + IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10, "0000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 100, "000", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 1000, "00", IF(COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) < 10000, "0")))) + COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)

  • MCorbin
    MCorbin Overachievers Alumni

    Thanks - that would work....

    =COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row) would give a sequential date, but I've used that formula (or one similar) before for other things and have had issues with performance getting bogged down when my sheets get large (with that said, it's something Smartsheet is working on).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =COUNTIFS([PO Date]$1:[PO Date]@row, [PO Date]@row)


    Should not give a sequential date but a sequential number based on the date.


    In all reality... Any formula can cause performance issues on larger sheets. A lot of it depends on what else you also have going on. I have one sheet that is only about 700 rows long. There are a handful of basic formulas in every row (to include a variation of the above). That doesn't slow down too much. It's when I add in the additional 20x23 section of wicked complex formulas that it runs REALLY slow. It can take upwards of 10-15 minutes for the sheet to process something as simple as the addition of a finish date. Ugh! I too have reached out to Smartsheet and gotten the same reply that they are working on the performance issues. Hopefully they are able to upgrade sooner rather than later.

  • MCorbin
    MCorbin Overachievers Alumni

    Oops - that was a typo - number, not date.

    My formulas must not be too complex - so far this one (or the one that was similar) was the only one that gave me a performance issue.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm surprised that shorter one gives you an issue. Either way...


    It will work to produce your sequential yyyymmdd######, but with the drawback of it causing you performance issues. I can't think of any other way to accomplish what you are wanting though.


    I do wonder if maybe it has to do with dates because all of my more complex formulas that cause issues are date based.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @MCorbin

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I wanted to generate a new 4 digit number (restarting at 1000) every calendar year.

    I did this by creating 4 dummy rows at the start of my sheet. (I don't know why it took 4 dummy rows to continue the formulas with each new entry but it worked.)

    The Year Created column formula is =YEAR(Created@row).

    Row 1 4 digits formula is hardcoded at 996.

    Row 2 formula is =IF([Year Created]@row = [Year Created]1, [4 digits formula]1 + 1, 1000)

    I dragged this formula down to rows 3 and 4.

    Users request a new number via form that is added at the bottom of the list.

    My Certificate No. formula is: =[Cert # Preface]@row + " " + JOIN(YY@row:[4 digits formula]@row)

    You could "suck" the date out of the Created auto column to do your comparison.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Pam Grant I usually use a single formula with a helper column.


    I insert the auto-number column with no special formatting then use what basically amounts to a COUNTIFS to get my count for how many previous entries there were for the year in the Created column. A nested IF evaluating that COUNTIFS takes care of the zero fill, but in your case since you are starting at 1000, you would just need to add the COUNTIFS to 999 (so that the first entry for the year is 1000).


    ="CSANe" + (999 + COUNTIFS(Created:Created, YEAR(DATEONLY(@cell)) = YEAR(DATEONLY(Created@row)), Auto:Auto, @cell <= Auto@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!