Serial Number based on date

[Eksistenze]

Hello,

I am trying to generate a serial number for some entries based on a date that is entered in column H. I have done this using the formulas below:

(This is in my DATA sheet in column E where I just have sequential numbers in column D)
=YEAR('2021 CSEC DISCREPANCY LIST'!H2)&"-"&TEXT(INDIRECT("D"&(2+COUNTIF('2021 CSEC DISCREPANCY LIST'!A$1:A1,YEAR('2021 CSEC DISCREPANCY LIST'!H2)&"-"&"*"))),"000")

(Where the actual serial number goes in the main sheet)
=IF(COUNTA(B2:L2)=0,"ZZZ",'DATA (DO NOT TOUCH)'!E$2)

The problem comes when the entries are sorted, it creates a circular reference and breaks the formula.

I am trying to come up with a VBA solution that generates the serial numbers based on the year in column H so that each time a new entry is added, it ups the serial number by one.

Example:
2020-001
2020-002
2021-001
2020-003
2021-002

Thank you!

 

[Saurabhj]

Hi,

Check below code:

I have used year in column A and showing serial number in column B.

Sub showSeries()

Dim lastRow As Integer

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For rowno = 2 To lastRow
    ActiveSheet.Cells(rowno, "B") = ActiveSheet.Cells(rowno, "A") & "-001"
    For revcheck = rowno - 1 To 2 Step -1
        If ActiveSheet.Cells(rowno, "A") = ActiveSheet.Cells(revcheck, "A") Then
            ActiveSheet.Cells(rowno, "B") = ActiveSheet.Cells(rowno, "A") & "-00" & (CInt(Right(ActiveSheet.Cells(revcheck, "B"), 3)) + 1)
            Exit For
        End If
    Next
Next
   
End Sub

SerialNosBasedOnYear.xlsm
	A	B	C	D
1	Year	S.No.
2	2021	2021-001
3	2021	2021-002
4	2022	2022-001
5	2021	2021-003
6	2021	2021-004
7	2020	2020-001
8	2019	2019-001
9	2019	2019-002
10	2021	2021-005
11	2021	2021-006
12	2021	2021-007
13	2022	2022-002
14
15
16
data