Sales and Stock Sheets - Multiple products under 1 sales code


Hi People 

I have created a spreadsheet which contains all of our companies stock of packaging . This includes labels and film used in the production of fruit. We have almost 200 lines of stock spread across 6 or 7 supermarkets. For example please see below:

Market      Fruit          Description     Amount     Re Order Amount     Area     Supplier 

Tesco       Blueberry    Label             25000            10000                  LO         AB Labels

Asda         Cherries      Film               50000            25000                  PH        Xreme Film

On a seperate sheet i have a simple sales sheet as below :

Market      Product      Case Count      Pask Weight      Order 27/07      Order 28/07    

Tesco        Blueberry          12                 200g                   250                 362

A product like Tesco Blueberries would incorporate 2 different items of stock. For an order of 250 boxes ( 12 punnets in a box ) we would need to deduct 3000 labels and 3000 impressions of film from the stock sheet on sheet 1 . 

Basically, I am looking for a way of linking a sales figure ( 250 for example ) on sheet 2, it would then multiply by the 'case count' then deduct that figure from 2 different stock items on sheet 1 . 



You can do this but you will need a way to store how much of what is needed for each case or unit of a product; then you need to reference this data and update the sheet that holds the product inventory.

Basically, you need macros to make this work well or you need to use a program other than Excel, like Access.
don (rep: 1745) Aug 9, '16 at 10:41 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login