How to create auto numbering invoice templates

[Jane_Chameleon]

I am struggling to create an excel template for a client. I need it to automatically generate a new invoice number each time my client opens the document. The numbers need to be sequential and I would like to define the starting number. I have tried using this visual basic:

Private Sub Workbook_Open()
Range(“C2”).Value = Range(“C2”).Value + 1
End Sub

Where C2 is the cell the number needs to be automatically generated to but I keep getting a runtime error (see file here).

Can anyone help? I am using Office 365.

Thank you in advance...

[#2]

@Jane_Chameleon I think you're getting an error because you have an undefined sheet for that range.  Try defining the sheet before that RANGE function.

That said, your technique has a number of drawbacks including if they open it up and close it without 'creating' a new invoice the number increments, if they open an old invoice to look at it the number increments, if they open the template and create and print a new invoice but don't save it (or it fails to save) then the increment doesn't save and you get duplicate invoice numbers.

I have a similar system for POs and such but I base it off a LOG.  My LOG has a button to create a new item that will identify the new number  and pop up a form with basic info to populate the LOG.  That way the LOG will show each number and relevant info and then it will also open the template and pre-populate those values into the sheet for the user and even saves that new sheet in a given location with a fixed naming style so the folder shows all the items in a unified way. - works really well, but that is just me.

[#3]

Hi

Will someone please share an excel invoice document with auto numbersing

[#4]

@JohnnyP51  As I noted I created a Log that creates unique purchase requisition docs.  I am attaching it here because it can be easily modified for most any purpose and include the specific fields and requirements of what you need.  I'm no accountant so I don't know what fields are needed for invoices and if that would be standard across the industry (which I doubt).  I hope this helps.

This does require macros so they must be turned on and allowed for this file.  Also it assumes a 'standard' file system so the file is on that computer or mapped network drive (i.e. not set up for use on OneDrive but I have done that with other versions).  The 'blankPR' file must be in the same folder as the log and then it will create subfolders based on the year.  This is set up to create numbers in the format of YYYY-nnnn  where YYYY is the present year and then nnnn is unique numbers counting from 0001 up for that year.  Again, that could also be customized.  

[#5]

@mtarler 

Thank You

Much appreciated

JohnnyP

[#6 · Accepted solution]

@JohnnyP51 That all said, I'm thinking of a very simple macro similar to the one posted in the original post above that could work and address my concerns with numbering. Basically on open increment the number in the template (similar to above) but only if it is the Blank Template, then a) save the template and then b) use Save AS to save the template as a new file based on the invoice number and make a small change so it knows it is no longer the template (i.e. now someone can open this document and review/modify/correct it as needed without generating new numbers). 

Attached is a sample.  when you open it if you allow macros it will do as I noted above.  If you disable macros you can edit it and save/update the template.  If you trouble just use any created output file, make the changes you want, then type "TEMPLATE" into cell B2.  Note cell locations B2 and C3 as the invoice number are hard coded into the simple macro so if you want to move them you will have to tweak the macro itself.

[#7]

@Jane_Chameleon 

Creating an auto-numbering invoice template can streamline your invoicing process. Here's a step-by-step guide to help you set one up:

1. Choose a Software: You can use various software options like Microsoft Word, Excel, Google Sheets, or specialized accounting software like Invoicera or FreshBooks.

2. Design Your Template: Start by designing your invoice template. Include necessary elements such as your company logo, contact information, recipient's details, invoice date, due date, itemized list of products or services, quantities, rates, subtotal, taxes, total amount due, and payment instructions.

3. Add Auto-Numbering Field: In your template, designate a field for the invoice number. You can place it at the top or bottom of the invoice. In Excel or Google Sheets, you can create a formula to automatically generate invoice numbers.

4. Excel or Google Sheets Method:
- In Excel or Google Sheets, select the cell where you want the invoice number to appear.
- Enter the formula for auto-numbering. For example, if you want the first invoice number to be "INV-001", you can use a formula like: `="INV-" & TEXT(ROW(A1),"000")`.
- Drag the fill handle (a small square at the bottom right corner of the selected cell) downwards to apply the formula to multiple cells. This will automatically generate sequential invoice numbers.

5. Word or specialized software method:
- In Microsoft Word or your accounting software, find the option for inserting fields or variables.
- Look for options related to auto-numbering or sequential numbering. You may need to consult the software's documentation for specific instructions.
- Set up the auto-numbering feature according to your preferences.

6. Save Your Template: Once you've designed and set up the auto-numbering feature, save your template for future use.

7. Test Your Template: Before using your template for actual invoicing, test it to ensure that the auto-numbering feature is working correctly. Generate a few sample invoices to confirm that the invoice numbers are being generated as expected.

8. Customize and Finalize: Make any necessary adjustments to the template based on your preferences and branding guidelines. Once you're satisfied with the design and functionality, finalize your template for regular use.

By following these steps, you can create an auto-numbering invoice template that will save you time and help you maintain consistency in your invoicing process.