Raw Material Stock management using excel help

[Icecream Man]

I am currently trying to construct a table of raw materials that will automatically adjust based on information entered about productions.
The system currently consists of 3 excel sheets:
1. A general ledge of all finished product stock in and out. This consists of columns for date, product name, number of batches made and a description that defines what happened i.e. Production is used when product is made, sale with details when it is sold, etc.
This table works well as I have set it up to automatically record total stock levels for our 3 product lines.

2. Our product formulations. Simply a list of ingredients and quantities used to make one batch of the product. Consists of all raw materials in one column and the amount of that material used in a batch of product.

3. The raw material stock table. Consists of columns titled (raw material name, unit of measure, stock on hand and additional columns for other details.

My goal is to link these 3 tables so that the "stock on hand" column for raw materials will automatically update based on the information I enter in Table 1 when we produce product.

I.e. We make a batch of product and consume raw materials. In Table 1 I enter the date, product made, how many batches we made and the description "Production".
Instead of manually updating the "Stock on Hand" column in Table 3 for each individual raw material, I want to develop a formula that will identify the product made (Product name column in Table 1), identify the description that identifies a production (Description column in Table 1 reads "Production"). The formula then takes the formulation under the identified product name (Table 2), multiplies it by the number of batches and then subtracts it from the "Stock on Hand" column in Table 3.

Can anyone please advise how to achieve this?

I have tried a few different attempts using IF formulas with nested AND formulas but to no avail.

Appreciate the help.

Cheers
Icecream Man

 

[Rijnsent]

Hi Icecream Man,
so for your raw materials you have a starting inventory from which you want to deduct if it is used by filling in finished product? How many different ingredients are there?

So going by your name:

1) products:
date - product - batches
22-1-2019 - vanilla ice cream - 2
21-1-2019 - strawberry ice cream - 3

2) formulations (table)
Name - ingredient - quantity
vanilla - milk - 6
vanilla - vanilla - 1
vanilla - sugar - 3
strawberry - milk - 6
strawberry - sugar - 2
strawberry - strawberry - 1

3) stock on hand
ingredient - start stock - usage in period - leftover stock
milk - 12 - ? - ?
vanilla - 10 - ? - ?
sugar - 10 - ? - ?

There are multiple routes you can take. A simple one is e.g. adding columns to sheet 1: one for each ingredient. Next, add a column to table 2 with a helper value, merging the product and ingredient, e.g. "strawberry_sugar" and "vanilla_sugar". In sheet 1, do a VLOOKUP on that table with the column header (the ingredient) and the row title (the product) to pick up the quantity needed per batch. See this for help: https://exceljet.net/formula/vlookup-with-multiple-critiera
Next, for sheet 3 you can use SUMIFS to pick up that data from sheet 1. Define for yourself what the period is, you can e.g. pick up all milk used in january, see https://www.mrexcel.com/forum/excel-questions/601474-sumifs-date-range-criteria.html on how to use that formula with date ranges.

Hope that gets you started,

Koen
The next step is to

 

[Icecream Man]

Thanks Rijnsent!
I've applied your suggested approach with some modifications to fit my current data layout and it has worked quite elegantly. Appreciate the help!

Cheers