Incrementing numbers in Excel

Incrementing numbers in Excel

Didn't find your answer?

I want to produce two Excel templates for a simple sales invoice and for a purchase order to replace our previous pre-printed (and expensive!) forms. These were pre-numbered as part of the printing process. How can I create a cell that includes the number of the document, which increments by one each time it is used. The solution could be a formula, VBA code (at which I am a novice) or even a commercial product. Can any Excel expert help?
Roger Levy

Replies (8)

Please login or register to join the discussion.

avatar
By AnonymousUser
20th Feb 2003 16:02

Thanks!
Aha! Found it. Thanks chaps! (invoice.xlt, if anyone else is looking. It may end up in a funny place if you are using NT/W2000/XP and not logging on as administrator.)

Matthew: I "know how" to get past the barrier, but I'd better not post it here, else Aweb will be in trouble. Mail me: [email protected].

:-)

Thanks (0)
avatar
By AnonymousUser
19th Feb 2003 14:13

Excel spreadsheet solutions?
Try clicking on file, new, spreadsheet solutions. There are invoice and PO templates there. They may not look all that pretty, but time spent on the appearance rather than reinventing the wheel may be more appropriate here? Autonumbering code is included with both templates.

Thanks (0)
avatar
By AnonymousUser
19th Feb 2003 14:15

Bit of VBA
Pretty easy really

1. Create a folder called Invoice and within it create a file called InvoiceNumber.xls

2. Type the next new invoice number in cell A1 of Sheet1. (Don't worry this is a once only task, but obviously we need a starting point.)

3. Press Alt+F11 and double click on the "This Workbook" node for InvoiceNumber.xls. (Make sure it says "InvoiceNumber -[ThisWookbook (Code)]" in the title bar.)

4. Copy and paste in the following code

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Dim number
number = Range("A1").Value
number = number + 1
Range("A1").Value = number
Range("A1").Select
End Sub

5. Save and close this file.

6. Open your invoice template file and in the cell where you want the number to appear type this formula:

='C:\Invoice\[InvoiceNumber.xls]Sheet1'!$A$1

in the cell where you want the number to appear.

7. Press Alt +F11 and double click on the "ThisWorkbook" node for your template (check the title bar) then copy and paste in the following code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Invoice\InvoiceNumber.xls"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

8. Save the template

This is very quick and dirty, and could be much improved (or done in other ways), but it will work as long as you don't have people trying to use it simultaneously on a network. You may of course want to edit the path (e.g. to a network path for the InvoiceNumber file) or use different filenames.

Re the Excel template, I don't think that does autonumbering, but maybe I'm looking at the wrong thing? Access has a free template for an entire order entry system: you'll need something like that if you want multiple simultaneous users.

Thanks (0)
Replying to frankie:
avatar
By dmcv
19th Sep 2011 14:33

INCREMENTAL NUMBERING

Hi

This is great

Thanks for your code

Presumably if I had lots of form with incremental numbers I could allocate a different cell for each form number on the same InvoiceNumber.xls thus only having to connect to one form and be able to use the same coding except for the cell number?

 

 

Thanks (0)
avatar
By AnonymousUser
19th Feb 2003 13:25

Auto Numbering
We done a similar thing for a client last year where they needed to create "batches" of numbers in a spreadsheet and increment across sheets/workbooks each time they loaded a new spreadsheet.

We got it to work by using a VB procedure linked to the machines registry this of cource means the roll-on autonumbering is tied to the machine, but it was only required for one user and we provided a user option to reset the starting numbers/ranges just in case.

For anything more you'd need database/cross sheet lookups etc. but the costs/timescales increase for this.

If you'd like us to look into this for your specific requirements you can email me directly on [email protected]

good luck

Alan Sheppard
(Bestfirm Limited)


Thanks (0)
By StephenElms
20th Feb 2003 23:05

Invoicing
Personally I looked at Excel for my business quite a few years ago. With further thought it was apparent that Access provides more information - as well as an auto-increment facility.
Having established an Access database of names, addresses, e-mail and fax numbers, it is then quite easy to creat the tables needed for invoicing and then relate it to your names.
The main (Access) advantages is that you can readily create data structures to view your information in different ways - that "what if'ing" can be very valuable.
Also don't forget that your can e-mail or fax invoices / data to a client easily - that's also true of Excel, of course - but an Access snapshot provides a permanent record of whatever has been sent...
Lastly if there is a requirement for data crunching of an higher order than is possible in Access, then you can export the data to Excell in any case.
I could go on for ages on the pros and cons, but please do remember this: a client name and address should only have to be entered ONCE... that's why we use Access for invoicing and letters and tax returns and reminders etc etc.

Thanks (0)
avatar
By AnonymousUser
20th Feb 2003 11:08

Auto numbers
Bob,

Don's got there first - I was going to say the same thing. The only point I'd like to add is that the autonumbering and other goodies appear in a floating menu. Can't comment on the XP version because I'm also running 2000.

Would love to get hold of the code if anyone reading this knows how.

Thanks (0)
avatar
By AnonymousUser
19th Feb 2003 18:14

Matthew
Could you explain where the autonumbering code is in the Sales Invoice template? I can't find anything that does this: perhaps I am looking at something different to you. I'm using Excel XP.

Thanks (0)