Formula for inventory tracking with Serial Number

[AlexLelio]

Hi,

I have an excel file to keep track of inventory.
1st tab is an overview of the quantity available for each item. column A "Product Code", column B "Description", column C "Qty on 12/1/12", column D "Qty In", Column E "Qty Out", Column F "Total on Hand".
2nd tab in the tab where I insert all items added to inventory which is reflect on the first tab's column D through a countif formula.
3rd tab is the one where I insert all items removed from inventory which is reflect on the first tab's column E through a countif formula.
Both 2nd and 3rd tab have these columns A Date, B Product Code, C Serial Number, D Location, E Cost. (we have 2 places where equipment is stored).
I would like to have 2 other tabs, each would list the items in each location. The idea would be that any time I put an item in the 2nd tab of the document, it would be added to the appropriate location and when this same item (recognized by serial number) would be added to the 3rd tab, it would no longer show up on the list of equipment available at the given location.

Your help is GREATLY appreciated! Please let me know if more clarification is needed.

 

[easy2understandexcel]

I think you could add 2 columns to the 2nd tab.
one column describes where that part/serial number is stored
the other column does a vlookup of the serial number to the 3rd tab. Then if you want to see whats on hand you just filter that column to exclude matches from the 3rd tab...

 

[AlexLelio]

Thank you. That sounds like a good idea. I'm not familiar with Vlookup formula though. Could you please elaborate?

 

[easy2understandexcel]

Thank you. That sounds like a good idea. I'm not familiar with Vlookup formula though. Could you please elaborate?

Here is an introductory video to vlookup:
https://www.youtube.com/watch?v=M8-FkSxhxO8