Results 1 to 2 of 2

I want to create a stock list using Excel?

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 ...

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    2

    Default I want to create a stock list using Excel?

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default

    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.
    Regards
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com