Home

Dynamic Inventory Management in Excel

Sakana
New Contributor

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

3 Replies

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

🔥 Join thousands in my amazing Single Click Dashboard Reports Masterclass Here: http://bit.ly/Excel_Dashboard_Masterclass 💯 Get 100 Of My Best Workbooks in One Zip File Here: http://bit.ly/100_Workbooks 📗 The Ultimate Excel Resource Guide, with 1000 Excel Resources, is the last Excel resource you
See Part 2 Here: https://youtu.be/GodLw1LfTj4 🔥 Join thousands in my amazing Single Click Dashboard Reports Masterclass Here: http://bit.ly/Excel_Dashboard_Masterclass 💯 Get 100 Of My Best Workbooks in One Zip File Here: http://bit.ly/100_Workbooks 📗 The Ultimate Excel Resource Guide, with 100...

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.

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?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies