I want to create a stock list using Excel?

ultraclean

New Member
Joined
Jul 7, 2005
Messages
2
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.
Thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top