Generate the next number on these sequence

[mark692]

hi guys is there a formula that will generate the next number on these sequence,
the sequence will be like this
ex.
40120-1
40120-2
40120-3
40120-4
and so on and so on

40120 is tha date today
the number after 40120 is the sequence i want to change every time i add another transaction

 

[footoo]

.

 

[mark692]

.

whats that sir?

 

[footoo]

whats that sir?

Sorry. I posted the wrong information so deleted it.
I think you will need a VBA solution.
How are you inputting the numbers at present? Manually?
For the number sequence at the end, do you need to re-start at 1 if the date number changes?

 

[Peter_SSs]

every time i add another transaction

What does that mean? Remember we have no idea about the data or layout of your worksheet. What about posting a small set of dummy data and the expected results with XL2BB?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[mark692]

Here is the sample layout that i want. i want to make a transaction number sequence, i want in cell B3 to generate the next highest transaction number on range E3:E7.

my transaction number consist of the date of the day and the number of that tranasction
ex. 040220-1, 040220(is the date today) -1 (transaction no. 1)

thanks in guys

Book1
	A	B	C	D	E	F	G
1
2		Next Transaction Code			Transaction No	Cash
3		040220-6			040220-1	5000
4					040220-2	6000
5					040220-3	2000
6					040220-4	2500
7					040220-5	3500
8
9
10
Sheet1

 

[Peter_SSs]

What happens tomorrow? Do you just add another value in column F and want the Transaction number in column E to start again at one with tomorrow's date?

If so, then as footoo indicated you would need to use vba to do that. Is that an acceptable way to go?

If not, then what does happen tomorrow?

 

[mark692]

What happens tomorrow? Do you just add another value in column F and want the Transaction number in column E to start again at one with tomorrow's date?

If so, then as footoo indicated you would need to use vba to do that. Is that an acceptable way to go?

If not, then what does happen tomorrow?

tomorrow will change the transaction code to 040320-1, and the transaction number will start to 1 with tomorrows date, yes VBA will do sir thanks!

 

[Peter_SSs]

See if this Worksheet_Change event code does what you want.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim vSplit As Variant
  
  Set Changed = Intersect(Target, Columns("F"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsNumeric(c.Value) And Len(c.Value) > 0 Then
        vSplit = Split(c.Offset(-1, -1).Value, "-")
        If vSplit(0) = Format(Date, "mmddyy") Then
          c.Offset(, -1) = vSplit(0) & -(vSplit(1) + 1)
        Else
          c.Offset(, -1) = Format(Date, "mmddyy-1")
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[mark692]

See if this Worksheet_Change event code does what you want.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim vSplit As Variant
 
  Set Changed = Intersect(Target, Columns("F"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsNumeric(c.Value) And Len(c.Value) > 0 Then
        vSplit = Split(c.Offset(-1, -1).Value, "-")
        If vSplit(0) = Format(Date, "mmddyy") Then
          c.Offset(, -1) = vSplit(0) & -(vSplit(1) + 1)
        Else
          c.Offset(, -1) = Format(Date, "mmddyy-1")
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

thanks for this sir but i need the transaction code to appear first on the "next transaction code" cell because i have something to do with it, i dont want it to appear on the table, thanks