New Contributor

Hi everyone, is it possible to have sequential numbering on excel forms?

EG. I have created Delivery Dockets via excel but need sequential numbering on these.

It would be great if once 1 x is created with a number, then when the next one is to be created, the next docket would automatically change to the next number.  Is that possible?

8 Replies

Hi @tpengineering-2231


You could autofill the sequence down the list and populate each row with the delivery docket information.


You could also have a formula in the number cell to produce your sequential number (e.g. =A1+1). This will just add one number to the cell above it as you create new docket lines.


There's probably a programmable way to do it but I'll leave that to the folks who love doing that.


Have fun!





Please check attached file.


This is a table that adds autonumber when some one enters a value in B2 cell.


Hi everyone

Maybe I did not explain the question properly.

I was looking at sequential numbering on 1 x sheet at a time.

The same as if producing invoices in an accounting system.

Not sequential numbering per cell.

Thanks for your replies.

Selma :)


Hi @tpengineering-2231


Oh then I have nothing to help with that. Hopefully another expert on the forum might have something to assist you with.


Good luck and best wishes!





Ok. Thanks Damien.


this is a difficult task, I have usually create a macro that does the job aside from other tasks like linking data. as any formula will cause a circular reference. which means these numbers will update themselves whenever they are recalculated at best.



I too am trying to figure a way to do this. Hope someone out there has an answer


For this task you need to know some basic programming knowledge. I can share a code to get new numbers from a database however before using this code in your macro there are several steps you need to know and apply:


1. You need to have a workbook acting as a database where you have all the numbers stored

2. You need to open this database workbook before you run this macro whether manually or by macro itself

3. The first sheet of the database workbook is the sheet where all the numbers and relative data is stored

4. there must be a row of headings in this sheet.

5. this code only gets a new number and the last empty row and saves them in MewRecordNumber and RecordRow as public variable which you can use in other modules in the same macro workbook. So it is up to you to write additional code to save this information to your database.



Option Explicit
Public NewRecordNumber As String 'the new record number that we generate
Public RecordRow As Double 'to store where we need to add the new number on database
Private DefTxt As String 'this is the default string that is always in the beginning of the number
Function GetNewNumber() As Boolean

'record number consists of a default text, year and 4 digit number that starts from 0 every new year
'example: DefStr-18-0001
Dim Database As Workbook
Set Database = Workbooks("Database.xlsx")

Dim DBSheet As Worksheet
Set DBSheet = Database.Worksheets(1)

DefTxt = "DftStr"

RecordRow = DBSheet.Cells(DBSheet.Rows.Count, "A").End(xlUp).Row

'this is to search if there are any default text in the number we create
'if there are no defalut text in the number you can ommit these lines
If InStr(1, DBSheet.Cells(RecordRow, 1).Value, DefTxt, vbTextCompare) = 0 Then

GetNewNumber = True
Debug.Print NewRecordNumber
Exit Function
End If
'end of searching defalut text

Dim LastRecordNumber

'splitting the last record number in to an array by - sign
'I will have an array of 3 values which:
' LastRecordNumber(0)=Default text
' LastRecordNumber(1)=Year
' LastRecordNumber(2)=4 digit number
LastRecordNumber = DBSheet.Cells(RecordRow, 1).Value
LastRecordNumber = Split(LastRecordNumber, "-")

'I am checking the year of last record number and if it is different than current year
'I will create a new number
If CInt(LastRecordNumber(1)) <> Right(CStr(Year(Date)), 2) Then
GetNewNumber = True
Debug.Print NewRecordNumber
Exit Function

CreateNewNumber (LastRecordNumber)

End If

Debug.Print NewRecordNumber
GetNewNumber = True

End Function

Private Sub CreateInitialNumber()
NewRecordNumber = DefTxt & "-" & Right(CStr(Year(Date)), 2) & "-0001"
RecordRow = RecordRow + 1
End Sub

Private Sub CreateNewNumber(LastRecordNumber As Variant)

Dim No As String
'lastrecordnumber(2) has the last number that is in the database
No = CStr(CDbl(LastRecordNumber(2)) + 1)

'if the number is 1 digit I add 0's to have a four digit number as string
If Len(No) < 4 Then
Dim Say As Integer
For Say = Len(No) To 3
No = "0" & No
Next Say
End If
NewRecordNumber = DefTxt & "-" & LastRecordNumber(1) & "-" & No
RecordRow = RecordRow + 1

End Sub

Related Conversations
Convert Days to whole months
SPC123 in Excel on
1 Replies
Copy down formula issue
NJ1000 in Excel on
1 Replies
Change data source
kevom in Excel on
0 Replies
Excel Formula Help....
shardae in Excel on
0 Replies
Kayla_Carter in Excel on
0 Replies