I am trying to create an order system using Microsoft Excel, but can not seem to find any help on the problem.

What I need to achieve in theory is very simple.

Having created the look and feel of the template order form and I would now like to have a way of automatically inserting an order number in the nominated cell reference that follows on from the previous order number, without the need to update it myself.

Should I be using some kind of database within Excel to enable this function to work?

I have tried using the on-line help facility, but my search terms are not producing the type of information I require.

I have also looked in MS Office - Inside Out reference manual, but drawn a blank.

I am sure this action can be done, its just I don't know how.

Any help will be very much appreciated.

Originally posted by HellBoy
Should I be using some kind of database within Excel to enable this function to work?


IMHO you should be using a database system fullstop.
This would be a doddle in MS Access (there's probably even a demo you can rip off)

OK - I know that's no help but ...tools for the job and all that.

How do you intend to record all the separate orders?
Are they just 'rows' in your spread sheet?
What about the order's items?

I'll shut up now and let the Excel experts tell you how to do it.

Nomme

Use microsoft access the order numbers auto increment.

Its a doddle to learn, and far superior to excel.

As suggested it would be better to use MS Access or another relational database for an order system but it's still completely possible in Excel.

Obviously I don't know how you plan to store your orders but personally I'd set up a seperate worksheet to keep track of them - 1 row per order if possible. If you then use a column to store the order number, let's say column A for now you can get the next order number by using the following formula


=COUNT(A:A)+1


What this does is counts the number of cells in column A with numbers in them and then adds 1 to give the next order number. Bear in mind the order number will have to be a number only, no letters etc. otherwise COUNT ignores it. Obviouisly it means you can't delete any orders either otherwise it will screw up but there are ways round that i.e. use a field to flag orders as deleted/cancelled but keep the information in the worksheet. Also if you wanted to start your orders on a number other than 1 then just modify the +1 part e.g. +1000 will start your order numbers on 1000.

Hope this helps, any questions and I'll try to answer them.

Just use access, far easier.

What fields are you planning on needing/using... what information you planning on keeping on record? How do you plan to use this data?

If you give me enough info I can probably whip you up an Access based database in a few hours, of course if its a lot of work then you'll have to do it yourself, I cant afford to waste lots of my time for free, but if its simple I don't mind :)

I've had to do two order-style databases so far at Uni, its pretty easy once you've got all your data normalisation done.

If it's only a basic flat database HellBoy needs he may as well stick with Excel if that's what he knows as the data manipulation tools are pretty good.

Thanks for all the advice, I think I have got it nailed.

Fynksy wotsis name has it.
OP may as well use Excel as a flatfile database.

He can do something with the view to make it appearl like a form and then enter as needed.

He could also start filling out a worksheet and insert the autocalcs and sheet protections to make it bombproof.

Perha[ps a compromise would eb to do it in a database using something as simple as works.

If you definitely want to use Excel to do this then I would post here.

http://www.mrexcel.com/board2/

But as above, I would suggest a database solution.

Regards

Adam

If you don't want to mess about with multiple worksheets you can simply incrememnt the PO number when you click save by adding a macro to the save event.

Open your Excel sheet, press alt+f11 to get into the VBA editor.

Right click on ThisWorksheet on the left and choose "View Code"

paste this code in


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("B5").Select
ActiveCell.Value = ActiveCell.Value + 1
End Sub


Change the B5 to the cell that your PO number is in.

Now every time you click save the PO number will have 1 added to it.