Dynamic Inventory Management in Excel

Hello,

I'm looking for some guidance on how to build a dynamic inventory system. What I have is a workbook with 5 sheets that represent different contracts. Each sheet has the inventory for each contract as a table. These item types overlap between contracts and have internal part numbers that link them but they also all have different serial numbers and property numbers so each item it tracked individually per contract.

What I'm looking to now build is a master list on a different sheet that can compile a list of all the different items from all the contracts and tell me how of each single item I have in stock. I would like this master list to be able to dynamically change has I add or subtract items from each individual contract.

I feel like this should be possible given the nature of tables but I'm just not familiar enough with Vlookup or other Table related functions to pull this together and I'm just spinning my wheels. I've tried using pivot tables but because my data overlaps in each contract it's hard to link it all so it looks right.

I attached a very simple version of what I'm working on. Hope someone out here has a good way to handle this.

Thanks