Creating an auto-generated sequential number

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

Look where? Put the result where?

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

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")

Bob Phillips said:

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")

Click to expand...

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

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"

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")

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.