EXCEL SEQUENTIAL NUMBERING ON FORMS

Copper 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?

9 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!

 

Cheers

Damien

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!

 

Cheers

Damien

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

CreateInitialNumber
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
CreateInitialNumber
GetNewNumber = True
Debug.Print NewRecordNumber
Exit Function
Else

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

@erol sinan zorlu 

This helped me very much.  Thank you for posting the downloadable file.