MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple purchase order system in excel

Posted by Martin Mc on August 30, 2001 7:57 AM


Is it possible to have a simple purchase order excel sheet which allows me to generate a unique numbering system. I would also need to export the information to a database which stores p.o. number, date created, and the amount ordered.

I am not very familiar with SQL or Access so i hope it is possible to do something simple through excel.

Thank you for any help you may be able to give me.


Posted by Dax on August 30, 2001 10:28 AM

What you could do is something like this:-

Say you're PO number is in cell A1 of your worksheet you could use a little VBA code to increase this number by 1 each time a new PO is requested.

Start by opening the VB editor (Alt+F11), then click View, Project Explorer (it may already be visible).

In the project explorer double click the ThisWorkbook icon associated with your PO workbook.

You'll be presented with a blank code module. Now assuming your sheet name is 'PO'. Try this code:-

Private Sub Workbook_Open()
Sheets("PO").Range("A1").Value = Sheets("PO").Range("A1").Value + 1
End Sub

This means that the workbook needs to be closed and then opened again if you want to create another purchase order. You could have a button on your worksheet and then assign a similar macro to the one above.


Posted by Martin on September 03, 2001 2:45 AM

Yes, that's great for the numbering. Is there anyway of recording the date and the po description in another file?