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

You were so close! I took your original tables and added one more column to them: Contract. Then I consolidated the tables to the Master List.

I put a put a slicer on this so you can see the individual contracts and make it easier to update.

I also made a pivot table from the data and put slicers on it too. The two slicers are independent of each other.

On the dashboard, you can look at the individual contracts. Or you can filter on parts in use. Or if you want to see how much of a part is in use, filter for that part number. It might be easier to see this information in one place, so there is another sheet with pivot tables on the Summary sheet. The Contract Slicer for the dashboard also controls the pivot table on these sheets.

Slicers are really easy. They are fancy filters but they also allow you to control multiple pivot tables at once.

The really are quite intuitive. Select a field in the pivot table and then on the INSERT ribbon select Slicer. You can almost follow the prompts from there. When you right click on the slicer you can look at options and properties. I am sure a web search can provide a more detailed explanation than I just did.

As for the rest, I will have to re-read what you just posted and digest it. Take a look at the Summary sheet. With all contracts selected, that is a list of each item only once and its total.

In answer to the first question, you can overlay the results of a pivot table with a named dynamic range and use it as the source data for the secondary pivot table. Here is some information to get you started: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

In answer to the second question, there is PowerPivot. I've played with it a little but am by no means an expert. I suggest doing a web search on it, take it as far as you can and if you still need help then re-post the question with the new sample workbook.