PDA

View Full Version : Consecutive Numbering Excel Invoice



David57
08-06-2007, 10:31 PM
I have created an invoice in excel and want to provide a consecutive number each time i open the invoice. Can anyone tell me how i can achieve this. If it requires VBA then i am not familiar with this and would appreciate any reply to be in depth if possible.

Thanks in advance.

pctek
09-06-2007, 02:30 PM
You could type it in each time.
Not hard.

winmacguy
09-06-2007, 02:37 PM
You could type it in each time.
Not hard.

Not hard but not always accurate if your doing a lot of docs or invoices at different times on different days and multiple clients. The only reason I say that is because I am in a similar situation and would be interested in knowing what formula to use.

Graham L
09-06-2007, 03:06 PM
Would autofill (computing.fandm.edu/training/excelx/autofill.php) do what you want?

winmacguy
09-06-2007, 03:15 PM
Thanks for the suggestion Graham, but I use that already for columns. I that the solution is more likely a formula in the cell that is able to generate a new invoice number from a templated doc every time a new invoice is generated without having to remember what the previous invoice number was.
eg:
Client A Inv No.00003
Client B Inv No.00004
Assortd clients Nos. 00005-12
Client C Inv No. 00013 etc. all generated from Invoice template doc 00000.

Graham L
09-06-2007, 03:27 PM
I'd imagine that this is a common requirement. Perhaps you're supposed to use a commercial accounting package which includes such features. ;)

I'd suspect that a spreadsheet is not the most appropriate software for accounting and billing. A database would probably be able to handle the "client #, invoice #" easily in its reporting module; Word would do it. But they're not accounting software either.

But someone will have made something to twist Excel's knobs to do it. It will just be a matter of finding the right keyewoprds for Google. :D

winmacguy
09-06-2007, 03:40 PM
Your probably right there Graham, but thanks anyhow. :) Looks like a google search might be the answer.

beeswax34
09-06-2007, 03:44 PM
How come David57 asked the question yet its winmacguy handling all the replies? One and the same?

Graham L
09-06-2007, 03:51 PM
perhaps this http://www.google.co.nz/search?hl=en&q=excel+invoices&meta=

wotz
09-06-2007, 04:42 PM
Perhaps you're supposed to use a commercial accounting package which includes such features. ;)

Yep, too many cheap people think excel if the answer for all their financial needs.

David57
09-06-2007, 04:47 PM
No David57 and winmacguy are not one and the same.

winmacguy
09-06-2007, 05:02 PM
How come David57 asked the question yet its winmacguy handling all the replies? One and the same?

I just happened to be interested in David57's question, possibly for the same reason. I am helping the guy I work for to set up his billing and invoice system in OpenOffice and he was asking me how to do template numbering.

David57
09-06-2007, 05:30 PM
I am of the opinion that the consecutive invoice number should be able to be created the same way that the date can be and that is by way of a formula however i wonder if there is any excel whizzes out there that can help.

David

godfather
09-06-2007, 05:39 PM
The consecutive number inremented each time it was opened would be a nightmare I suspect.

It would (like the date field in Word) increment any time the sheet was subsequently opened as well, so your financial records would be incorrect as to invoice number.

To circumvent that would require something clever enough to recognise that the sheet was initially opened, and after that event the figure would have to be changed to a normal constant in any subsequent opening.

It sounds quite horrible.

beama
09-06-2007, 08:01 PM
Write a macro that combines the date and customer code ie 020107BMA striping the formating from date (2/1/07 beama), yes a little vba would be needed .
Andrew or Parry should be able to help do this they are the gurus in this area.

Google, also, should reveal some code snippets to do this.

It may be possible to use the formula functions within excel to do this such as left(), right() , now() and concatenate() functions but I'd have to play with it, if I get time.

beama
09-06-2007, 08:23 PM
not quite want you specified but may do the job

in the cell you want the invoice number

=CONCATENATE(LEFT(TODAY(),6),B1) B1 holds the customer code

didn't take that long after all :cool:

winmacguy
09-06-2007, 09:53 PM
not quite want you specified but may do the job

in the cell you want the invoice number

=CONCATENATE(LEFT(TODAY(),6),B1) B1 holds the customer code

didn't take that long after all :cool:

That looks very interesting, I'll have to try it and see what happens :) :thumbs:

beama
09-06-2007, 10:45 PM
just knew google would have the answer

http://www.mrexcel.com/td0031.html

chiefnz
10-06-2007, 10:18 AM
Hmmm, I use a program called Invoice2Go... I got it off an old APC Mag...

It is a free full version, If you're interested I'll upload it to Rapidshare or something like that with the registration link for you. It's a great program and I use it all the time for my clients.

PM me if you want to give it a whirl.

Cheers

Clark
10-06-2007, 10:29 AM
on a side note... I used to invoice in excel but changed to cashbook complete. I find it great for tracking invoices, something that you can't do easily in excel.

JonB
10-06-2007, 10:56 AM
Hi

Have a look at this, it should do the trick

http://ukww.net/patools/excel/autonumbering.htm

JonB

Graham L
10-06-2007, 02:12 PM
Producing invoices with Excel might be ... like a dog walking on its hind legs. It is not done well; but you are surprised to find it done at all. Samuel Johnson was talking about women preaching, but that was a few hundred years ago, before there was Excel.

andrew93
10-06-2007, 10:31 PM
Write a macro that combines the date and customer code ie 020107BMA striping the formating from date (2/1/07 beama), yes a little vba would be needed .
Andrew or Parry should be able to help do this they are the gurus in this area.

Thanks for the vote of confidence beama!

That link you provided to MrExcel should do the trick so there's no need to reinvent the process......

Cheers
Andrew