Solved

# Creating consecutive numbers to be used as invoice numbers

Posted on 2009-03-31
587 Views
Hi everyone,

I'm using an excel spreadsheet to record customer data that will eventually be populating a pdf form. I need to have a column in excel that will create consecutive invoice numbers for each customer record.

Can someone show me how to do this if it's possible in excel?

Appreciate any help.
0
Question by:gwh2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2

LVL 29

Expert Comment

ID: 24027401
you could type an initial value in a cell and then go to Edit / Fill / series and click on "step value" and enter 1.
0

LVL 1

Author Comment

ID: 24027429

I just tried your suggestion. I have 1 initial record and one of the column headings is called invoice_no. I went to the first record (row) and clicked in the cell below the heading called invoice_no and typed in an initial value, ie. GO0001. I then went to Edit > fill > fill series and put in 1 next to "step value" as you suggested. But then when I click in the cell below the one I just created, nothing happens, ie. the next value doesn't come up.

Do you know what I'm doing wrong?
0

LVL 50

Expert Comment

ID: 24027460
If you put
="Go"&TEXT(ROW(),"0000")
in A1
and copy down
then yuou will get an incrementing list Go0001, Go0002 etc
Cheers
Dave
0

LVL 29

Expert Comment

ID: 24027463
Ahhh so we aren't talking numbers strictly then
you will need to create some sort of formula that will take the numeric portion of your invoice value, add 1 to it then string it back together.... something I couldn't do off the top of my head and would need to Google.
A quick qorkaround may be to span the invoice value over 2 columns with 1 column just having the GO and the next column having the step value
0

LVL 29

Expert Comment

ID: 24027467
oops qorkaround = workaround!
But ignore that, Dave has hit the nail on the head.
0

LVL 1

Author Comment

ID: 24027503
Thanks - the formula works great but what if I need to have a heading in A1? If I put the formula in A2 it starts at GO0002. Is there a workaround?
0

LVL 50

Accepted Solution

Dave Brett earned 500 total points
ID: 24027534
You could try this variant
="Go"&TEXT(ROW()-ROW(\$A\$2)+1,"0000")
where \$A\$2 is always the first cell in your list (so A2 gives 1, A3 gives 2 etc)
or just
="Go"&TEXT(ROW()-1,"0000")
to hardcode subtracting 1

Cheers
Dave
0

LVL 1

Author Closing Comment

ID: 31564752
Perfect! Thanks a million.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll