# EXCEL: Create a auto number for invoicing



## lisabryant (Nov 8, 2000)

I want to create a template for invoicing that will automatically give me the next invoice number to use when I create a new invoice. Can anyone tell me how I create a automatic number for invoicing? Or where can I can find information regarding this matter.


----------



## Mulderator (Feb 20, 1999)

Have you considered using Quickbooks? It's very inexpensive and does a much better job at what your trying to do (i.e., billing, collections, etc.). Anyway, Excel has an Invoice Template that you can take a look at. Just Choose File, New, Click "Spreadsheet Solutions" and choose Invoice.


----------



## cdevaro (Aug 19, 1999)

I have a similar situation and would like to know if there is any way to put a formula in a cell to issue a number that stays with a record. I am creating a database list and would like Excel to issue a number for a particular record. I have seen the invoice template in Excel, but what I need is slightly different. If there is a way to this please let me know.


----------



## Anne Troy (Feb 14, 1999)

I've got an answer for you, CD, and I'm determined to get an answer for Lisa.

CD, here's an example: Put Invoice# in cell A1, put 1 in A2.

You don't have to do this formatting part: Click on the letter A to select the column. Hit Format-Cells-Number tab. Click Custom on the bottom left. In the skinny box, delete "general" if it appears, and type 0000 (or however many numbers you would like in your "invoice" or number scheme.

Put your first invoice (or record of data) in Row 2. In A3, type the following formula:

=if(isblank(b3),"",a2+1)

Then, as soon as you type something in b3 (and it is no longer blank),it will perform the A2+1 and give you a number 2, right? Right. You may want to protect that column. Hope this helps.

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench


----------



## lisabryant (Nov 8, 2000)

I have seen this template and basicly that is what I want to be able to do. My invoices are already in Excel (small and very basic), but I would like to use my own invoice layout. Thanking you for your time.


> quote:
> 
> Originally posted by Mulder:
> *Have you considered using Quickbooks? It's very inexpensive and does a much better job at what your trying to do (i.e., billing, collections, etc.). Anyway, Excel has an Invoice Template that you can take a look at. Just Choose File, New, Click "Spreadsheet Solutions" and choose Invoice.*


----------



## cdevaro (Aug 19, 1999)

Thanks for the help Dreamboat, but the problem with this is that I will have a list of products on order and from this list, once they are shipped, I will have a macro move them from products on order to a products shipped (and invoiced)list. When I move the list, I would start back from the beginning. If you can think of another way to autonumber or of a better way to set up my system, please share.


----------



## Anne Troy (Feb 14, 1999)

Got your answer, Lisa. Here's the code and instructions:

Private Sub Workbook_Open() 
With Range("A1") 
.NumberFormat = "00000" 
.Value = .Value + 1 
End With 
End Sub

Open your invoice file or a blank workbook. Hit Alt-F11. On the left column of the visual basic editor, double-click "this worksheet". Then, on the right, paste the code above. This code is designed to put the number in A1. Just replace the A1 with the cell you want your number in.

Then, type the number you want your invoices to start with (to test, put a 0 in cell a1). Put all your other invoice info in and save the file as a regular workbook. Close the workbook, then open it. You'll notice the number becomes number 00001. You'll have to save the workbook each time so it retains the last invoice number. If you mess up, you just don't save the file. You'll probably want to record a macro too that will wipe out all the invoice info (after printing and/or saving to another file), save and close your workbook.

If you need some real help, just email your invoice to me. I'll get it going for you.

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench


----------

