Need Help Building a Dynamic Inventory Sheet Formula

[jstarbird]

Hello!

I cannot seem to figure out how to make such an advanced formula work. Could someone with Excel mastery help show me how to nest them properly?

I'm looking how to make a formula that changes the dynamic inventory based on what I put into the changes section by referencing 14 different box kits and pulling what they contain (multiplied by changes quantity) out of inventory when they are entered into the changes section.

'Realtime Inventory'

'Starting Inventory'

'Assembled Boxes' (1 of 2)

'Assembled Boxes' (2 of 2)

I was thinking I need a formula in the Quantity Section of Dynamic Inventory, but I can't figure out how to do this. I just need to be able to type which box and quantity in the changes area and have it pull anything within the appropriate table out of dynamic inventory. (I have dynamic inventory referencing the starting inventory values from sheet 2 so that they can be added to or subtracted from manually when more product arrives within the starting inventory sheet).

[#2]

Still struggling with this; is it even possible in Excel?

[#3]

I will save all datas in a sheet of database and run sql to retreive relative report.
As to table layout,3 Columns journal is an option rather than keep multiple tables.
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-daily-balances/m-p/3754060#M182874

[#4]

The goal here is to not have to enter the actual inventory more than once. Will that allow me to just type in a table, and have it deduct the entire table contents from current inventory? I'm not familiar with what you mentioned.