PDA

View Full Version : Solved: Numbering by date



paris_guy
06-26-2006, 12:08 PM
Hi guys
I'm working on a project to design an invoice that starts numbering by the first day of the month.(i.e. 06060001 for the beginning of june 2006)....:dunno can anyone help me to make it restart numbering at the last day of the month keeping in mind the 30,31 days months.....:help thanx in advance..

mdmackillop
06-26-2006, 01:51 PM
Interesting little problem. Try this attachment which uses the registry to save invoice numbers. By playing around with the day and check values, you can test for restart numbering on any day.
Regards
MD

paris_guy
06-27-2006, 07:52 AM
Thanx for the effort man ...I think it needs some modifications ...you can have a look to the attachments...waiting for response.

paris_guy
06-27-2006, 08:00 AM
Sorry man ... I attached ur invoice...try again..one more thing if u don't mind..can I hide the "0.00" in the amount column when there no values in the "unit price"and "QTY" cells ......

mdmackillop
06-27-2006, 08:36 AM
Is 0606 mmdd or ddmm?

To hide "0.00"
=IF(E13<>"",PRODUCT(E13:F13),"")

Bob Phillips
06-27-2006, 08:49 AM
Try this

paris_guy
06-27-2006, 09:04 AM
0606 is yymm......the rest are the invoice number(4digits)

paris_guy
06-27-2006, 09:30 AM
I think I didn't explain the full story...sorry guys...
my project is to design an invoice that:
1- protects the main cells
2- starts its first number at day one of the month in the format of yymmxxxx (e.g at july 2006 it'll be "06070001")
3- Calculates the product of the QTY and Unit price without showing the "0.00" if they were blank.
4- Prints 2 copies
5- Increments the invoice number and inputs selected details such as the number, issued to and amount fields into another sheet after (or before)the print
6- Clears fields for new invoice

It can be similar to the one in this article but with some changes http://www.vbaexpress.com/kb/getarticle.php?kb_id=348

Good luck guys...thanx in advance

Bob Phillips
06-27-2006, 09:35 AM
Save the file as a template and then it does all you want does it not?

paris_guy
06-27-2006, 09:40 AM
Try this

yeh man....but how??:think: is it that complicated??it looks simple but i couldn't do it.....:banghead: ...can u explain??
</IMG></IMG>

lucas
06-27-2006, 09:51 AM
What if you have two invoices for the same day?

Bob Phillips
06-27-2006, 10:02 AM
Here is a template file.

Load it in your Excel templates directory, then you can invoke it each time as a fresh file from File>New (not the New icon)

paris_guy
06-27-2006, 10:03 AM
What if you have two invoices for the same day?

the day is not involved in the number only yy and mm. so in a day there can be a number of invoices.

mdmackillop
06-27-2006, 10:37 AM
Here's a version with some added automation for you to play with. I've used XLD's numbering code 'cos it's neater than mine was. The clearing and copying, I'll let you attempt. Not too difficult with the macro recorder and a bit of searching.

paris_guy
06-28-2006, 07:47 AM
mdmackillop...:clap: ....NICE JOB MAN...I'm Amazed by the capability of the program and your ability to take advantage of it....can u lock the fields "time, invoice numberitem, description, unit price, amount, total& balance" cuz the formula is deleted if u delete the content of the cells.(I tried to protect the sheet but then the drop menu was disabled).

mdmackillop
06-28-2006, 01:34 PM
I don't see the problem with protection.

paris_guy
07-03-2006, 07:36 AM
mdmackillop.....thanxxxxxxxxxxxxxxxxxxx :thumb......very helpfull.

paris_guy
07-03-2006, 11:11 AM
mdmackillop... Two more things if u don't mind.........I'd like to add some more features to the invoice....
1- with this version, the invoice number increments only if printed from the PRINT button and NOT if printed from the file menu. can it be done through both?
2- can the invoice be auto saved after the print (to keep the last invoice number)
thanx again and again....

mdmackillop
07-03-2006, 12:43 PM
Hi Paris-Guy,
These can both be done, but we need some input from yourself. Post your own attempts to solve these problems and I'll be glad to assist further. The macro recorder and a study of the code should help.

Bob Phillips
07-03-2006, 04:11 PM
mdmackillop... Two more things if u don't mind.........I'd like to add some more features to the invoice....
1- with this version, the invoice number increments only if printed from the PRINT button and NOT if printed from the file menu. can it be done through both?
2- can the invoice be auto saved after the print (to keep the last invoice number)
thanx again and again....

For my information, can you tell me why you would want the invoice number incremented after/before printing? If you printv the same invoice twice, it gets two different numbers.