PDA

View Full Version : Counter In Excel



28-05-2001, 11:06 PM
I am making an invoice in Excel which has an Invoice number field in it. Is there any way I can get Excel to automatically put a number in that field and count upwards so that each invoice has a different number?

Thanks

29-05-2001, 02:18 PM
To remember the last invoice number you'll need to store it somewhere. I notice that your next post mentions storing details in a database, so you could store the invoice number in there using a script in the invoice template.

eg. You could have a script in the excel template that is automatically run each time it is opened. That script looks in your database for the highest recorded invoice number, increment it, and insert that value into your invoice.

You'd also need to make another script that is executed when the invoice is saved/closed/printed or similar to save the fields into your database.

However all this is complicated and a bit arse-backward. My gut feeling is that if you really want this level of automation you should either have the database engineered (I assume you are using an Access database) to generate the invoice from the data, or look into getting a purpose built application like MYOB.

31-05-2001, 04:44 PM
Been fiddling around with exact problem myself. Haven't cracked the number increment - but check out the addin 'Template Wizard with Data Tracking' - you have to instal it off the Office CD. Seems you can set up a template to write a worksheet(your invoice) which will write selected cells to a seperate database worksheet.

If you figure out the number increment - wouldn't mind hearing about it!

mgardiner
11-03-2005, 04:31 AM
The Template Wizard with Data Tracking was a good tip, thanks. Microsoft offers it for download on the Office website.

The Wizard helps you create a template that automatically saves a field to another file, so for instance it might write an "invoice #" to an xls (just a primitive database in this case, I called mine "Invoice Template Database.xls"). Now when you create a file from this template, it will write out the value of the appropriate cell when you save the file. Give it a nice dummy value to start, say 115.

To close the loop you must edit the template to lookup the max value in the new database. Open the XLT file and change the manually entered 115 to a formula like this:
=MAX('C:\Invoices\[Invoice Template Database.xls]Table1'!A:A)+1

Now when you create a new file from the template, it will find the max value in the database and add one. When you save the file, it will ask if you want to update the database--if you say yes, the incremented number will be stored in the "database" so the next time it will be the new max. If you ever make a mistake, you can open the xls "database" and trim the numbers back.