Inventory Management

**pratikiitd** · 07-23-2010, 09:07 AM

Hi

I am a newbie trying to develop an inventory management system.

Suppose I have two sheets. Sheet 1 contains the information about all the inventory in hand. Column A contains the information about the product ID and column B contains the information about the corresponding quantity available. EX: I sell three items so A column has three entries with product ID AA123, AB234, BC235 and the column B contains 3 entries with 23, 34, 45 which are the quantity available.

Now suppose in a day I sell 2 items AA123 (qty. 10) and BC235 (qty. 5). This entry is being made in sheet2 where column A contains the product ID and column B contains the qty. sold. I want to search the product ID of column A of Sheet2 with Sheet1 and subtract the qty. in sheet2 from sheet1.

The final sheet1 should look something like this (after sell has been made):
Column A: AA123, AB234, BC235
Column B: 23-10=13, 34, 45-5=40

Please suggest me some way out.

Thank You.

**oOarthurOo** · 07-23-2010, 09:54 AM

Here's a workbook with what you descibe. For your further education here are the features or elements of Excel used to make it:
1) Dynamic Named Range
2) Vlookup
3) Paste > Link
4) sumif
5) Lists (Data >Insert >List)
6) Data Validation

Hope this helps,
AM