Results 1 to 8 of 8

Thread: Creating an auto-generated sequential number

  1. #1

    Cool Creating an auto-generated sequential number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Look where? Put the result where?

  3. #3
    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

  4. #4
    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")

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    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

  6. #6
    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"

    Click image for larger version. 

Name:	Screen Shot 2014-05-12 at 1.55.21 pm.jpg 
Views:	2 
Size:	84.4 KB 
ID:	2330

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

  8. #8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •