+ Reply to Thread
Results 1 to 4 of 4

How to automatically assign next available number

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to automatically assign next available number

    Hi all,

    I've worked my way through the best part of a new tendering register spreadsheet with the help of searching through this forum, though I'm at a bit of a loss with the last function I wish to add. Any help would be appreciated.

    I am trying to assign an individual tendering number automatically from either a macro or a picklist if it can be done.

    Column A will consist of 4ea x different business units i.e. Projects, URD, Communications & Workshop.

    I would like to be able to assign an individual number for each division i.e.
    A2:PROJECTS B2:P00001
    A3:URD B3:U00001
    A4:COMMUNICATIONS B4:C00001
    A5:WORKSHOP B5:W00001
    A6:PROJECTS B6:P00002
    (and so on)

    Once the a specific number has been used it can not be used again.

    Any advice would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: How to automatically assign next available number

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to automatically assign next available number

    Thanks for the reply. However I didn't elaborate further as the spreadsheet is a little more complicated.

    1. All new tenders and current tenders being worked on come into the NEW & CURRENT tab.
    2. A specific tender number needs to be assigned when a specific Business Unit is picked i.e. PROJECTS as per below:
    A5:PROJECTS B5:P00001
    A6:URD B6:U00001
    A7:COMMUNICATIONS B7:C00001
    A8:WORKSHOP B8:W00001
    A9:PROJECTS B9:P00002
    (and so on)
    3. If a tender is declined for whatever reason, a macro has been formulated to remove the entry from the NEW & CURRENT tab & dump the entry into the DECLINED tab
    4. If a tender is accepted, completed and a date entered into column L, then a macro will automatically remove the entry from the NEW & CURRENT tab & dump the entry into SUBMITTED tab
    5. Choosing either WIN or LOSS within the SUBMITTED tab will remove the entry and dump into the corresponding WIN or LOST tab for keepsakes
    6. The issue with the formula =left(a2,1)&text(countif($a$2:a2,a2),"00000") is that once the entry is removed and dumps into the other tabs the assigned numbers automatically resets as they have no reference

    Apologies for the lack of information prior. I've re-uploaded the complete sheet.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to automatically assign next available number

    Any further help on this one?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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