Google Answers Logo
View Question
Q: Consecutive Numbered File Labels in MS Office ( Answered 5 out of 5 stars,   0 Comments )
Subject: Consecutive Numbered File Labels in MS Office
Category: Computers > Software
Asked by: needsomeinfo-ga
List Price: $4.00
Posted: 26 Sep 2003 11:53 PDT
Expires: 26 Oct 2003 10:53 PST
Question ID: 260543
I need to make file labels that are formated with the number YYMM###. 
So October 1st 2003, we'll start with the number 0310001 and go up
0310002, 0310003...0310100, 0310101, and so on.  Then starting
November 1st, the numbers will begin with 0311001, 0311002, and so on.
 Right now we're using MS Office to format the page for the labels and
then individually typing each number.  I'm wondering if there is a way
to get Office to automatically enter the numbers as a sequence.  I
expect that we'll have to make the change for the months and years,
but I'd like to be able to find a way that we can just set the basic
format and then have the computer count up for us.  If there is a
better way to do this in Excel that is fine too.  Please let me know
if additional clarafication is needed.

Request for Question Clarification by efn-ga on 27 Sep 2003 12:29 PDT
Would an explanation of how to generate the numbers in Excel be an
adequate answer, or do you also need to know about how to get them
from Excel to be printed on labels?

Clarification of Question by needsomeinfo-ga on 28 Sep 2003 00:29 PDT
I do need to know how to get the numbers from Excel into a format that
I can print the labels.  If I can print from Excel onto the sheets of
labels, that is fine.
Subject: Re: Consecutive Numbered File Labels in MS Office
Answered By: maniac-ga on 30 Sep 2003 18:29 PDT
Rated:5 out of 5 stars
Hello Needsomeinfo,

You should be able to format the data properly in Excel, so I'll
describe the steps to
 1 - set up a spreadsheet that will to generate the numbers from
today's date
 2 - format the cell to match the size / layout of the labels
 3 - print a test page to check the result before printing the labels

#1 - Open a blank spreadsheet and enter the following formula into the
first cell (A1)
What this does is take today's date - extracts the month number puts
it into the first two digits of the result, the day number into the
next two digits, and leaves four digits for the serial number. All the
rest of the cells can have a simple formula to add one to the previous
cell in the series. So - cell A2 will read
cell A3 will read
and so on. I don't know if you have just a single column of labels -
if not, do multiple columns / rows to match your label paper. Adjust
the counting formula in each cell to match the layout of labels.

If you don't want to use today's date - change today() to a date
string (e.g., "1/1/2004"). If you don't want to start the label
sequence with one, change the +1 at the end with the "right number".

#2 - Now you will note that in months 1 through 9 (January through
September), you only get a single digit for the month. If you need two
digits, select the range of cells and use Format -> Cells -> Number,
select Custom, and enter six zeros (000000), then OK. That will force
the numbers to be displayed / printed with six digits. While in Format
-> Cells, I also suggest updating
 - alignment (center vertical / horizontal) as you like it
 - font (style, size)
 - border (none)
 - pattern (only if you want one - otherwise, leave blank)
When you change the font size, the cell will automatically grow to be
large enough for the number but won't be the right size for the
labels. With the cells still selected, then use
 - Format -> Row -> Height to change the height of the row. This is in
"points" - 72 per inch, so if you measure the distance between labels
with a ruler - you can put in the right number without guessing. For
example, a 3/4 inch high spacing between the top of labels would be 54
 - Format -> Column -> Width to change the width of the row. The
number here is also in points.

#3 - At this point, take a white sheet of paper (the same size as the
label paper) and print a test page. Place it on the labels (aligning
the corners) to check the layout. You may have to adjust the page
margins (Page Setup -> Margins), if the number in the first cell does
not line up on the first label. After the first label is right, adjust
the row height / column width if the other labels don't line up

At this point, you should have a spreadsheet that will print the
numbers for your labels with the date / serial number increasing as

To research this answer, I used the Excel help, searching for
  format number
  row height
  column width
  page margin
and similar phrases to get the functions (in the first cell) and the
formatting information needed to produce the answer.

Don't hesitate to ask for clarification if this answer does not meet
your needs. Good luck with your labels.

Request for Answer Clarification by needsomeinfo-ga on 30 Sep 2003 19:24 PDT
Your answer was for the format of Month, Date, File Number; however,
as indicated in my original question the format I need is Year, Month,
File Number (i.e., 0310001).  Could you please give me a corrected
formula that will give me file numbers for this format?

Clarification of Answer by maniac-ga on 30 Sep 2003 19:32 PDT
OOPS! My mistake.


To explain the first part - mod is a function that provides the
remainder after dividing by the second number. So mod(2003,100) will
be 3.

needsomeinfo-ga rated this answer:5 out of 5 stars
I got exactly what I needed and exactly what I asked for.  Thanks for
the detailed answer and responsivness to my request for clarafication.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy