This is a discussion on I want to create a stock list using Excel? within the Excel Questions forums, part of the Question Forums category; I use Excel for invoicing a book keeping. I want to keep an up to date Stock list for our ...
I use Excel for invoicing a book keeping.
I want to keep an up to date Stock list for our business.
I use an exact copy of our order form for making invoices, ie each item is listed as per the order. These are generated as a new Excel file for each invoice.
I have another Excel file with all our stck listed.
I just want to update this each time an invoice is completed.
I could put a SUM in each box, but that only works when you have the invoices completed.
All invoices are named 'invoice***' ***=number.
Can i enter a formula to look at all invoices between no: 1 & 99999?
The cell location never alters, only the file names.
There are too many variables here to give a specific answer.
A stock control system does not just consist of "Goods Out", you need to consider "Goods In" too. You also might like to include 'Orders' you have placed for goods as well as 'Outstanding Orders' you have placed. Also 'Customers Orders Outstanding'
Current Stock = 'Goods In' minus 'Goods Out'.
Using Excel as a database, the ideal method is to think in terms of a simple table with headings in row 1 and data underneath. You can then use the very powerful tools available like formulas, filters, pivot tables etc. to do the hard work of analysing data. The 'Description' of each stock item will need to be spelled exactly the same in each record to enable this. Data Validation might be useful here.
You start your stock record by doing stocktaking and putting the amounts as 'Goods In'.
A simple table might look like this. See how you also get a "picture" of what is happening. A simple filter to just show, say, Apples, would be even clearer. :-
Code:Date Reference Description In Out ====== ========= =========== ===== ===== 1/1/05 Delivery 1 Apples 5000 2/1/05 Delivery Pears 2000 2/1/05 Inv 1234 Apples 500 2/1/05 Delivery 3 Apples 2000 2/1/05 Delivery 4 Bananas 1000 3/1/05 Inv 1235 Apples 100 3/1/05 Inv 1236 Apples 300 3/1/05 Inv 1236 Pears 100
One method of finding the current stock of Apples in the above table would be to use the formula :=
=SUMIF($C$2:$C$9,"Apples",$D$2:$D$9) - SUMIF($C$2:$C$9,"Apples",$E$2:$E$9)
Hope this helps.
BrianB (using XL2003 & 2010)
Most problems occur from starting at the wrong place.
Use a cup of coffee to speed up all Windows processes.
It is easy until you know how.
**FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY