Excel...Purchase Orders

[Welderpaul]

I'm faffing about with excel, re doing a lot of forms and that.

We have a purchase order system, and up to now we have manually generated an order number and written it on the sheet. I would like excel to generate sequencial numbers somehow, ideally each time the blank template purachse order is opened.
Is this possible?

 

[Luke]

You could do it using macros quite easily. The macro would run when you open the template, increment a cell, and save itself.

Would that do what you wanted? Of course you need to be careful with macros as they can be used to execute malicious code, but as long as you keep an eye on when macros run it'd be fine.

 

[breezer]

or you could do it with some if then else processing.

If B1 is not blank then B2 = A2 +1

Column 1 being the date or something like that, and column 2 being you PO

 

[Chipper]

Easy if they're all in the same sheet. Or if you had one sheet/file just to store order numbers, and separate files for invoices, the invoice files could probably reference the number file and increment it. Not sure.

I used to use Word, better layout control. New invoice = new doc, named with the invoice # in it's name (and date) so dead easy to see what was next. Like 427_04-08-10.doc or some such.

Or better overall is get a cheap accounting system like MyOb, does invoices, and you get other functions like see all your overdues at a glance, run all sorts of reports and so on. Can't remember how much it was but £30 springs to mind, there's probs a fair few out there.

 

[Luke]

My understanding was that Paul had a template for one complete purchase order, so there's no row-by-row increment as such. Each save-as of the document is a new PO.

You could copy the main details to/from another spreadsheet acting as a register.

 

[JimM]

It's a relatively simple macro to write in VBA, you will need to have a 'log' sheet but this can be hidden and you'll never know it's there.

I've pm'd you with my email address, if you send your template over I'll knock a macro up for you.

Cheers

Jim

 

[Luke]

You don't need a log sheet, just to increment the PO number.

It might be a useful feature to have though.

 

[Welderpaul]

Thanks all/Luke/Jim, this forum really is a very helpful place.

JimM has done a sterling job and got us up and running exactly as i need.

Again, thanks!

 

[bill_gj]

Have a look at this:

http://excel.tips.net/Pages/T003336_Generating_Unique_Numbers_for_Worksheets.html

 

[Luke]

Thanks all/Luke/Jim, this forum really is a very helpful place.

JimM has done a sterling job and got us up and running exactly as i need.

Again, thanks!

Glad you got it sorted