Thread: [Solved] automatic invoice numbering
View Single Post
 
Old 08-13-2013, 09:01 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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:
Code:
=TEXT(TODAY(),"yyyymmdd")&"-VT"&TEXT($X2,"0000")
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.

Last edited by BobBridges; 08-13-2013 at 09:06 AM. Reason: correct typos
Reply With Quote