Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sales and Stock Sheets - Multiple products under 1 sales code

0

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 . 

Answer
Discuss

Discussion

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: 1989) 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