+ Reply to Thread
Results 1 to 3 of 3

Create column of tracking numbers from cells

  1. #1
    Registered User
    Join Date
    01-13-2006
    Posts
    2

    Create column of tracking numbers from cells

    On an excel spread sheet that my customers will fill in several columns of data I want to create a column of consecutive tracking numbers that will uniquely identify each line of data. The list needs to be prefilled in and cannot be changed by my customers.

    The tracking number will consist of 3 parts: the current date which will be posted in a cell; the customer file code (4 or 5 Alpha characters) which is posted in a cell and a consecutive number series starting with the number
    "001".

    So the first number in the series of numbers would look like this if the date was 03/16/2007:

    03162007WBDG001
    03162007WBDG002

    Can this be done?

    thanks

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    What I would suggest is to write a Worksheet Change event handler procedure, something like this (at least it can be used as a starting point):
    Please Login or Register  to view this content.
    Some relevant information regarding the above procedure:
    • Vendors will input data into cells D2:D1000 (this range calls the change event handler procedure)
    • Vendor id is in column B
    • Tracking numbers will be entered into column G
    • In order to increment the tracking numbers, it is assumed above that data will be entered in row order. So if the same vendor enters different orders on the same date, the procedure will search up from the applicable cell to find the most recent order number, and increment it by 1.

    HTH

    Jason

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    This would put it all in one cell

    =TEXT(TODAY(),"MMDDYYYY")&"WBDG"&TEXT(ROW(A1),"000")

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

+ 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