Continue Count When Row Is Moved To New Sheet

C. Ruiz

Hi,

I'm attempting to create an auto-number row that takes into account what year it is for a ticket submission form sheet. To do this I realize I must use a formula instead of the autonumber column. However, I want the auto number to continue once a row is moved to a new sheet (The row gets moved and deleted from the original sheet once the ticket has been marked complete). My current formula to output "2023TIC-X" is:

=YEAR([Date Ticket Was Created]@row) + "TIC-" + COUNTIFS([Date Ticket Was Created]:[Date Ticket Was Created], AND(@cell <= [Date Ticket Was Created]@row, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row)))

However no where in this do I take into account the ticket numbers on the completed tickets sheet.

Samuel Mueller

@C. Ruiz This may be kind of an annoying thing to do every year but you could update your auto number prefix on jan 1 every year. So your prefix for auto number is 2023TIC- and then jan next year you change it to 2024TIC-

To do it how you are currently doing it you would need to use cross sheet references and also count the sheet that you are sending the rows to. If you are moving row it will keep the original created date so your formula should still work, just add on what's needed including the cross sheet references.

=YEAR([Date Ticket Was Created]@row) + "TIC-" + COUNTIFS([Date Ticket Was Created]:[Date Ticket Was Created], AND(@cell <= [Date Ticket Was Created]@row, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row))) + COUNTIFS({cross sheet ref to date created}, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row)))

modify my addition with the cross sheet reference you create to the other sheet - replace {cross sheet ref to date created} with yours

C. Ruiz

https://community.smartsheet.com/discussion/comment/357987#Comment_357987

@Samuel Mueller I updated the formula with:

=YEAR([Date Ticket Was Created]@row) + "TIC-" + COUNTIFS([Date Ticket Was Created]:[Date Ticket Was Created], AND(@cell <= [Date Ticket Was Created]@row, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row))) + COUNTIFS({Closed Tickets Range 1}, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row)))

But I'm getting the #Unparseable error. Any thoughts?

Samuel Mueller

@C. Ruiz I think I added too many parentheses, take off one very last parentheses.

=YEAR([Date Ticket Was Created]@row) + "TIC-" + COUNTIFS([Date Ticket Was Created]:[Date Ticket Was Created], AND(@cell <= [Date Ticket Was Created]@row, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row))) + COUNTIFS({Closed Tickets Range 1}, IFERROR(YEAR(@cell), 0) = YEAR([Date Ticket Was Created]@row))