Generate the next number on these sequence

mark692

Active Member
Joined
Feb 27, 2015
Messages
321
Office Version
  1. 2016
Platform
  1. Windows
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
 
I think this formula in cell B3 should do what you want...

=TEXT(TODAY(),"mmddyy-")&MID(LOOKUP(REPT("z",255),E:E),8,99)+1
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
Sorry, my mistake - I had not read your initial post correctly. You shouldn't need vba for that.

mark692 2020-04-02 1.xlsm
BCDE
1
2Next Transaction CodeTransaction No
3040220-3040220-1
4040220-2
5
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
B3B3=TEXT(TODAY(),"mmddyy-")& COUNTIF(E3:E100,TEXT(TODAY(),"mmddyy")&"*")+1
 
Upvote 0
Sorry, my mistake - I had not read your initial post correctly. You shouldn't need vba for that.

mark692 2020-04-02 1.xlsm
BCDE
1
2Next Transaction CodeTransaction No
3040220-3040220-1
4040220-2
5
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
B3B3=TEXT(TODAY(),"mmddyy-")& COUNTIF(E3:E100,TEXT(TODAY(),"mmddyy")&"*")+1

thank you sir! ill try this also
 
Upvote 0
this is perfect! thanks sir!
Are you sure? Wouldn't you have this if the first 2 transactions were entered on an earlier date?
In which case the suggested formula from post #11 does not re-start at 1?

mark692 2020-04-02 1.xlsm
BCDE
1
2Next Transaction CodeTransaction No
3040220-3040120-1
4040120-2
5
6
Sheet1
Cell Formulas
RangeFormula
B3B3=TEXT(TODAY(),"mmddyy-")&MID(LOOKUP(REPT("z",255),E:E),8,99)+1
 
Upvote 0
Good point Peter! The best I could come up with to handle that problem is this...

=TEXT(TODAY(),"mmddyy-")&IF(TEXT(TODAY(),"mmddyy")<>LEFT(LOOKUP(REPT("z",255),E:E),6),1,MID(LOOKUP(REPT("z",255),E:E),8,99)+1)
 
Upvote 0
Are you sure? Wouldn't you have this if the first 2 transactions were entered on an earlier date?
In which case the suggested formula from post #11 does not re-start at 1?

mark692 2020-04-02 1.xlsm
BCDE
1
2Next Transaction CodeTransaction No
3040220-3040120-1
4040120-2
5
6
Sheet1
Cell Formulas
RangeFormula
B3B3=TEXT(TODAY(),"mmddyy-")&MID(LOOKUP(REPT("z",255),E:E),8,99)+1


i see i get it, ill try to tweak it thanks guys for the ideas!
 
Upvote 0
Good point Peter! The best I could come up with to handle that problem is this...

=TEXT(TODAY(),"mmddyy-")&IF(TEXT(TODAY(),"mmddyy")<>LEFT(LOOKUP(REPT("z",255),E:E),6),1,MID(LOOKUP(REPT("z",255),E:E),8,99)+1)
[/QU thanks sir!

this is works thanks sir!
 
Upvote 0
Doesn't the simpler formula from post #13 do what you want?

no sir, because there are times that i need to put the same transaction number in the column, post #13 gives me the wrong transaction number in sequence like this

POS.xlsm
ABCDEFGHIJK
1
2
3nexttransactioncash
4040320-3040220-1
5040220-2
6040220-2
7040320-1
8040320-1
9
10
11
12
13
14
15
16
Costumer
Cell Formulas
RangeFormula
B4B4=TEXT(TODAY(),"mmddyy-")&COUNTIF(Table4[transaction],TEXT(TODAY(),"mmddyy")&"*")+1


instead of giving me 040320-2 it gives me 040320-3
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top