+ Reply to Thread
Results 1 to 3 of 3

Autonumbering a field of a table automatically using a non-traditional number format

  1. #1
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Autonumbering a field of a table automatically using a non-traditional number format

    Hello All,

    I am trying to do what I am sure can be done with a formula but not sure how to get it done.

    I have been tasked to change the way we track our job files. We have previously tracked the Job files in a word document which has the Job Number, the Job Name, and Job Creator. We assign each job a number which is the year first, a dash (-) and a sequential number. For example, the first job we did in 2013 would be 13-001, the second 13-002, etc. Part of the reason for conversion to excel was to automate some of the simpler tasks and making input more efficient and less prone to human error. As such, I would like my table to automatically fill in the "Job Number" field with the next number in the sequence (as in my above example, when the user tabs on the previous job, it would automatically fill in 13-003 for the Job Number).

    How to I go about writing a formula to get this accomplished? TIA for your help.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Autonumbering a field of a table automatically using a non-traditional number format

    Type in A1: 1
    A2=A1+1
    ...
    A100

    For the year 2013, highlight A1:A100, Ctrl-1 (Format cells)-Custom-Type: "13-"000
    Next year: "14-"000
    And so on
    Quang PT

  3. #3
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Autonumbering a field of a table automatically using a non-traditional number format

    Quote Originally Posted by bebo021999 View Post
    Type in A1: 1
    A2=A1+1
    ...
    A100

    For the year 2013, highlight A1:A100, Ctrl-1 (Format cells)-Custom-Type: "13-"000
    Next year: "14-"000
    And so on
    Works splendidly, thank you for that. Only problem is that it still won't autopopulate the next row when I tab on my table. In other words it isn't copying the formula down when I go to insert a new record into the table. It does copy a couple of data validation in two other fields though. Any insight on that?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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