Dynamic Inventory Management in Excel

[Sakana]

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

[#2]

This series of two videos I did on Inventory Management may help a bit. It does not have all of your requirements but gives you a basic understanding of what you are trying to achieve:
Part 1: https://youtu.be/-1N0L-FDWCs
Part 2: https://youtu.be/GodLw1LfTj4

[#3]

Thanks for the reply. I had actually already seen your videos. They just aren't quite what I was looking for, but did give me some ideas on how to accomplish most of what I want. I don't need all the sales related stuff, just a way to keep track of items on hand from multiple sheets.

I do wonder though, you manually named some columns. If I'm using tables that is basically the same thing as you did but the headers of the tables columns are the named ranges now for each sheet?

Also, maybe you have a solution for this. At this point I feel my full vision isn't quite possible in the way I feel it should be. So as a step back is it possible to just combine all the data from all my tables into one giant list that stays dynamic. Meaning if I add to one of the other tables, the master list sheet also gets the updated entry? So far all the ways I've seen to combine tables they are static.

[#4]

I had one other thought to ask.

Is there a function or series of functions that can look at a column and return how many of a duplicate entry there are. Or even better return the entry value and how many there are of it?