The page that Epinn points out is probably as good as any. Excel is a rather
poor tool for trying to track inventory. You'll notice that the actual
inventory management programs on that page are (mostly?) for Access.
It's almost impossible to give specific Excel information on doing it
without seeing how you have things set up at this point. The use of multiple
sheets for sales often complicates things beyond telling.
In it's most basic functional form you'd have one sheet that showed each
inventory item to be tracked, one per row. Probably a column for quantity
received, another for quantity sold/disposed of, and a third using the 1st
two to give you quantity on hand.
The problem gets to be in getting new purchases included in the quantity
received and new sales into the quantity sold cells. The easiest way to do
that is probably to have a separate sheet for each inventory item to record
purchases/sales on and roll up the totals for each sheet back to the master
list of all inventory items. This can be almost unmanageable if you have any
large quantity of inventory items at all.
Wish I could be more helpful, but as I said initially, the "how to update
the inventory list when some items are sold" is a question that has no real
answer without knowing how your workbook is set up at this point in time.
