+ Reply to Thread
Results 1 to 4 of 4

Auto generate next number for selected criteria

Hybrid View

Catniption Auto generate next number for... 12-09-2014, 12:07 PM
daffodil11 Re: Auto generate next number... 12-09-2014, 12:17 PM
Catniption Re: Auto generate next number... 12-09-2014, 12:21 PM
newbydeveloper Re: Auto generate next number... 12-09-2014, 12:27 PM
  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    15

    Auto generate next number for selected criteria

    Hello brilliant people! I am trying to auto-generate protocol numbers. Our layout is simple: current year, type of doc, then a sequential number. Ex: 2015-IQ-001. I need a formula that will create the protocol number by using the type of doc and adding the sequential number. So, if I have -001, then the next IQ doc would get -002. If they choose type PQ, then it would get 2015-PQ-001, then -002 etc.

    They may choose a type by a drop down list. I can get the "2015" and the type, but I do not know how to get the sequential number set up. I'm sure it's some kind of COUNTIF and +1 kind of thing, but I just can't figure it out. Please see the attached file for further explanation.
    Auto gen Protocol numbers.xlsx

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto generate next number for selected criteria

    E2:
    ="2015-"&VLOOKUP($D2,Sheet2!$A$1:$B$16,2,0)&"-" & TEXT(COUNTIF($D$2:D2,D2),"000")

    and copy down

    CountIf will count the occurences, and Text in 000 format will retain the leading zeroes as necessary.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Auto generate next number for selected criteria

    PERFECT! I love you guys! Thank you so much!

  4. #4
    Registered User
    Join Date
    12-20-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Auto generate next number for selected criteria

    Catniption, the formula for number sequencing is:

    =COUNTIF($D$2:D2,D2) for cell d2 and copy down for the rest.

    Edit: see my post was delayed here!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Auto Generate Next Number
    By sagar007 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-22-2013, 12:05 PM
  2. Excel 2007 : Auto Generate Next Number
    By wisedave in forum Excel General
    Replies: 6
    Last Post: 05-11-2011, 10:36 AM
  3. Auto Generate NUmber
    By Mooseman60 in forum Excel General
    Replies: 1
    Last Post: 10-03-2010, 10:52 AM
  4. number to auto-generate
    By dsammons3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2007, 03:48 PM
  5. Auto generate number???
    By iluminatae in forum Excel General
    Replies: 9
    Last Post: 02-11-2007, 07:43 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1