Inventory Management

  • 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.

  • Re: Inventory Management

    Try the attached. I did a simple macro and you probably will need to change the range in the marcro to match your data.

    I recommend you print out the inventory before posting the movements so you have a copy of what was posted.

    The macro will post the Ending balance to the Begining and clears the movements in the other files so new information can be entered.

    Ummm. can't attach

  • Re: Inventory Management

    Try the attached:

    Use L1 to expand the rows in the Inventory sheet.

    1. Be sure to have an item number on that row in column A and place a value in column J.

    2. Be sure to print out the movement before posting movements for they will all be removed and reset to 0. This give you a reference.

    I am still working on the file. I will add an icon to save before posting to allow you to go back if something goes wrong.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!