Looking for a solution to automate inventory management of product accessories.

[JuliaL1720]

Hello Excel Community!

I'm looking for some sort of formula to be able to create an additional sheet within my current Excel Book to best track the inventory of the accessories we need to sell our items. This is the current set up of our doc, with a master list that I do not currently have access to, but a coworker does, that manages all of the Data Validation for columns C-H. Each drop down has a variety of values we use regularly to manage our intake and so we can find each item if/when our Access Database crashes multiple times a day.

Effectively, I'm looking for some way to track the quantities of the bags in column D in a separate sheet, as well as the potential for 1-2 more columns for hoods and/or batteries. This will free us up a substantial amount of time instead of having to count bags each week.

I've tried a few other solutions I've found on here with minimal luck. If I have to rebuild the entire thing from scratch to make something work, I definitely am up to doing that if it's not possible with the current setup we run.

Thank you in advance for any help!

[#2]

Hello, based on the data above, could you provide the what you would like the sheet to look like? That would help me to know what you are trying to track. Thanks,

[#3]

@ReginaAnn 

I'm looking to create an additional sheet in this Book that will have our inventory of all of the Bag Types from Column D. This new sheet would have Column A through D as SKU, Description, Starting Quantity, and End quantity. I want to be able to connect it to the sheet in my original message so that when I choose one of those bag types, it automatically updates the inventory in the new sheet to reflect how many of each bag type remains.

I hope this makes sense, please let me know if you need additional info or clarification!

[#4]

@JuliaL1720 

Thanks for the reply.  Attached is a preliminary worksheet.  If you have anything like this that you've started it would be helpful.  I have a few questions.  Does the amount of bags on your list indicate beginning inventory?  How will outgoing inventory be indicated.  Where does the SKU come from. 

Thanks,

Regina