Inventory sheet with multiple versions of recipes and a long list of raw materials in each version

[inventory_guy]

I am very new to Excel formulas and started learning recently.
I am attaching a spreadsheet which i want to use for tracking the inventory of raw materials used in production.

Page 1. Raw materials Inward : Entries of incoming raw materials are going to be entered on this sheet. No confusion
Page 2. Production Entries : Manually entered, no confusion
Page 3. Recipes : Recipes of different items are entered along with different versions of the same Item. No confusion
Page 4. Raw Material Stock in Hand : I am stuck here. As per the raw material inward entries on page 1 and production entries on page 2, i want to apply formulas for the Raw materials stock in hand and the last date of stock movement (inward or outward)

I was trying XLOOKUP but that was not helping.

Can anyone please help me with that.

 

[Legacy 143009]

What will be the last stock movement?
Last production or last purchase? Or either of them?
Also can you share the sample file via wetransfer or other file hosting srrvice?

 

[inventory_guy]

What will be the last stock movement?
Last production or last purchase? Or either of them?
Also can you share the sample file via wetransfer or other file hosting srrvice?

Last stock movement is either last production or last purchase.

 

[inventory_guy]

What will be the last stock movement?
Last production or last purchase? Or either of them?
Also can you share the sample file via wetransfer or other file hosting srrvice?

Inventory Sheet.xlsx

Shared with Dropbox

www.dropbox.com