Hello,



I am trying to develop an invoice template in excel. Is there a way to have excel generate an automatic invoice number? We number our invoices with the year, and a consecutive number;
for instance 2012-004. The next invoice numbers would then be 2012-005, 2012-006, etc...

I have read tons of posts about this, from msot complex to most simple, but nothing really decisive...


Thanks

Enter 2012-001 in A1, and pull down as needed. Numbering will increase automatically

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The invoice numbers will be placed in a column (a list of invoice numbers), or will be in a single cell? The second option can be done only with a macro.
For creating consecutive numbers in your desired structure, you can use a formula like this:
=IF(LEN(A2)>0;YEAR(NOW())&"-"&TEXT(SUBTOTAL(3;A$2:A2);"00#");"")
(change ; separator to your regional settings)
Copy formula to your range...

Attached Files

New Microsoft Excel Worksheet.xlsx (9.6 KB, 44 views)

I am in the midst of creating an invoice using MS Excel 2011 (Mac Osx). I have populated one cell with the name 'Invoice Number' (H6) and would like I6 to be the cell which auto generates the invoice number. I have to comply with office standards so the formatting would be yyyymmdd-VT0001. I would like the ability for this number to increment itself by 1 every time a new invoice is created. And on that note, if you have an auto generated invoice number, please advise of the following;
a) once it generates the invoice number and let's say a week has passed and you realized you've made a mistake, will it try and create a new invoice number or will the invoice number remain unchanged?
b) is it better to create the formula and then save as a template? I'm just thinking for every time an invoice is created then I'd have to locate the last one which is a little long-winded.
c) at what point does the auto generated invoice know to increment itself?

Sorry, I'm a slight novice and the sheer volume of info on the net is misleading to say the least.

Thanks in advance.

Let's start with a disclaimer: I'm a Windows user, so I wouldn't want you to take me as authoritative. Still, maybe there are few differences between the two, externally. I'll talk as though we're in Windows, and you see whether you can apply it.

To create a list of invoice numbers is easy. In your case you want it to be yyyymmdd-VTnnnn; so in some helping column (say, X) you'd put the sequential part, starting with 1 and incrementing each time; then in the column where you want the actual invoice number, you concatenate it, perhaps like this:The problem here is that you want this invoice number to appear not in a list but in a single position on a form. Yet for controled documents, there must be a controler somewhere, a document that contains either the last invoice number used for each day (in your case), or better yet a list of all invoice numbers ever used. And either a given invoice has to be able to check that list, know which number applies to itself and display only that number; or some outside process has to put the value into the form and leave it there.

The first option might work like this: You create a master index of invoices, recording the number plus any other information about each invoice that you think belongs on such an index. Certainly that would include the path and filename of the document itself, and probably a title, a total amount, perhaps the initiator and/or final approver, whatever you end up deciding you need. The number itself would be created by some method like the above.

How does an invoice know which number to display? It would have to look in this external index for some other value. Someone involved with the initiation of the invoice would have to go to the master list, find the next unassigned invoice number and enter there the necessary information for the new invoice. Some of that information would have to be unique, perhaps the title or a combination of the title and date. Actually if the initiator is doing all this then in my opinion it'd be better for him to just cut-and-paste the invoice number form the control index back into the invoice itself; but we're talking about how to do it semi-automatically. So the invoice itself would have to have a lookup function that would find that title, or some other unique data, in the control document, and pull the invoice number from there.

Not only is this kludgier than you really want, it's also on the insecure side: A controled document ought not to be changeable. The way I half-heartedly suggest it above, all a user has to do is reword the title of an invoice, or correct the date, and it might suddenly start displaying a different invoice number. But once the number is assigned, and especially after the invoice has gotten into the system, that number should never be changed. If you're a sole proprietorship you can decide for yourself just how important that principle is; if you're keeping track of moneys handled by many people, it's indispensable.

An alternative is an external process that determines an invoice number and places that value on the form, not as a formula but as a literal value that must thereafter not be changed. That process could be a program unconnected with Excel that's run automatically once the document has been filled out and submitted. Or it could be a VBA program that resides in the Excel workbook itself, that the user runs when the invoice is filled out and ready to enter the system. Either way, the program would have to look up that control document I'm talking about, pick out the next usable number, record the information about this invoice (path and filename, title, whatever), and copy the invoice number into the Excel invoice form.

This second option is the one I favor, for the reasons I give above. It's more complicated, but I don't immediately see how to use the first option and still keep controled documents controled. And since this is an Excel forum, the next place to go (if you're still interested) is to talk about a VBA macro that would be part of your invoice template and would create a unique invoice number that would not change.

Please refrain from hijacking threads. Best to start a new one eventually referring to this one

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

CliveG, in case it's not obvious, by "highjacking threads" he means you really should have started a new thread rather than adding your question to the end of an old one. It probably isn't necessary to start a new one now, but it's something to keep in mind for the next time.

Good morning,

firstly apologies for 'hijacking' an existing case which may have already been resolved. It is my first time using the service and wasn't aware I was causing an affray. I'll remember the next time so as to ensure the monitoring police aren't so over zealous in their chastising.

Also, thanks for providing me with a response. I believe it to be useful and will try some of the noted options today.