Inventory Spreadsheet

Help !!!

 

I am “trying to” create an inventory signout sheet that keeps track of quantities (with multiple vendors). So each vendor has items and we have set it up that if you select the vendor from a dropdown then only the items associated with that vendor will come up….

The tricky part is I need to figure out how to deduct the quantiy of the item from my master list of inventory….

Lets say ACME Parts has a Muffler. We have ten of them in stock. I have the first sheet to be a signout, where the user would drop down Acme Parts, then select Muffler, then select the quantity they are using. Lets say one. 

I want the ACME Parts sheet with the quantity of stock to automatically deduct that one. So it would drop from ten to nine……

Thats the Goal… Im not sure if I should use a pivot table or if I should use IF formuals…..

In my workbook the first sheet is the signout sheet with the dependant lists, then I have 23 other seperate sheets, where each sheet has the Following headers vendors name A1, items A3 and QTY D4, with the data under those…..

the final sheet is a list data sheet where I created my lists from.

 

The Main sheet I have created lists goes like this:

The Headers are

A = Date (nothing special just a blank cell to type in)

B = A drop down list that you can choose the vendor (from a list on the last sheet of my workbook)

C & D are just blank cells to enter date

E = Dependant list where you Select the item based on vendor selection

So my questions is…. what is the best way to deduct the quanity being signed out listed on sheet one from the respective sheets with the inventory quantity

By: Nicole Roux

Leave a Reply

Your email address will not be published. Required fields are marked *