Creating an auto-generated sequential number

DeanCorp

New member
Joined
May 9, 2014
Messages
4
Reaction score
0
Points
0
Hi Everyone,

I am hoping you can help me, I would like to create an excel spreadsheet that can do the following.

Based on a users radio button input/option, it create the prefix "A" "B" "C" or "AB" etc.. and then for each of those prefixes it automatically look at the last highest number and provide the next sequential number and once it has created that number automatically or provide a button to email that created cell to the designated email address.

For example:
What is the type of document you are wanting to create? (has a drop down of A, B, C, or AB)

then if they had selected "A"

It would look at all the A's. .. A0081, A0082, A0083 and then since A0083 is the last one in the list, it automatically generate me A0084.

I hope I have explained this properly/clearly, if you need any further clarification please let me know.

Thanks in advance, I love this forum, been lurking for a while but couldn't find anything that was similar to my question.

Best Regards,

Dean
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Look where? Put the result where?
 

DeanCorp

New member
Joined
May 9, 2014
Messages
4
Reaction score
0
Points
0
I probably wasn't clear basically that it follows the next sequential number afterwards in the list in the next row down

E.g.
A0083
A0084
A0085
And then it generates A0086
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Nope you weren't and you still aren't, so I will guess

Use this array formula

="A"&TEXT(MAX(IFERROR(--(RIGHT(A2:A100,4)),0))+1,"0000")
 

DeanCorp

New member
Joined
May 9, 2014
Messages
4
Reaction score
0
Points
0
Nope you weren't and you still aren't, so I will guess

Use this array formula

="A"&TEXT(MAX(IFERROR(--(RIGHT(A2:A100,4)),0))+1,"0000")

Sorry Bob, thank you so much for being patient.

Basically I have data that looks like this

A00025 B1002
A00026 B1003
A00027 B1004
A00028 B1005

And I want to be able to generate automatically the next sequential number, so in this case A00029 and B1006.

Perhaps if I had a formula and maybe a "Generate" button it could look at the previous highest number apply some formula and generate the results A00029 and B1006.

I hope this makes better sense now.

Really appreciate your assistance and patience with me, I am new to Excel but learning pretty fast I believe :)

Thanks again
 

DeanCorp

New member
Joined
May 9, 2014
Messages
4
Reaction score
0
Points
0
Perhaps this makes better sense, I have uploaded some data. When I click that grey GENERATE button. It looks at the last highest number entered (in this case it is A0125) and then adds 1 being (A0126) and displays that number underneath "Next Available ID"

Screen Shot 2014-05-12 at 1.55.21 pm.jpg
 

atulkhandekar

New member
Joined
Jul 1, 2014
Messages
2
Reaction score
0
Points
0
If you required next available id in column "a" & in column "b" is suppose customer name.
If your customer name in "b2" then in "a2" type formula
=LEFT($B2,1)&TEXT(COUNTIF($B$2:$B2,LEFT($B2,1)&"*"),"0000")
 

atulkhandekar

New member
Joined
Jul 1, 2014
Messages
2
Reaction score
0
Points
0
If you have customer name in "b2", then you type formula in "a2" :
=LEFT($B2,1)&TEXT(COUNTIF($B$2:$B2,LEFT($B2,1)&"*"),"0000")
This is also you can use.
 
Top