+ Reply to Thread
Results 1 to 5 of 5

New Inventory list with balance forward / add / dilete / balance /year to date

  1. #1
    Registered User
    Join Date
    12-31-2010
    Location
    seattle, wa
    MS-Off Ver
    Excel 2007
    Posts
    4

    New Inventory list with balance forward / add / dilete / balance /year to date

    I am new working with excel 2007 and I need help to set up an inventory list.
    Assume the following:
    I have an inventory list in column D of 100 items with varying quantities starting as balance forward.
    Example: For item 1, there are 20 pieces in stock (balance forward) and for item 2, there are 31 pieces in stock (balance forward) and so on...

    Column E5 has the balance forward in it (existing previous inventory)
    Column F5 has the add amount in it (new goods manufactured)
    Column G5 has the delete amount in it (goods sold)
    Column H5 has the resulting balance in it. (result of E+F-G=H)
    Column J5 has the year to date goods manufactured (added from F5)

    I need a formula/code that copies the result of adding and deleting inventory (E5 & F5) to a new balance forward (H5) and then clearing the contents of the add and delete cells (F5 & G5) so every new entry in columns F5 & G5 is a continuation of the previous. This way I can input new goods manufactured in F5 and also I can input products sold in G5 and keep inventory total in H5.
    On top of this, I need a column eg.: Column J5 that keeps a yearly total of the goods manufactured column in column F5 and it totals the quantity produced per year. This column I need to reset at the beginning of each year.

    Thank you in advance

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: New Inventory list with balance forward / add / dilete / balance /year to date

    Attach what you have so far


    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    12-31-2010
    Location
    seattle, wa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: New Inventory list with balance forward / add / dilete / balance /year to date

    I have for example in column D the following item description: Soap Bar Lime 12oz
    In column E I have the starting quantity/inventory eg. 55.
    In column F I enter/add the new products manufactured eg. 45.
    In column G I enter/subtract the quantity sold eg. 20.
    In column H I need to get the curent stock/inventory which would be 80.
    In column J I need to get the year to date products manufactured which was entered in column F each time a new product was manufactured.
    I need to keep these 2 running balances on column H (curent inventory) and in column J (year to date manufactured) while I can put new/fresh data in columns F and G.

    Thank You,

    Daniele

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: New Inventory list with balance forward / add / dilete / balance /year to date

    What's so difficult about attaching a file, I haven't time to create an example from your post & I doubt if anyone else will

  5. #5
    Registered User
    Join Date
    12-31-2010
    Location
    seattle, wa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: New Inventory list with balance forward / add / dilete / balance /year to date

    Sorry, I had a little hard time to attach...

    I am explaining bellow better what I need:
    Assume the following: I have an inventory list of 100 items listed in column D. Each item varies in quantity starting as a balance forward (original inventory).
    Example: For item number 1, there are 56 pieces in stock (balance forward) which is listed in E5 and for item number 2, there are 48 pieces in stock (balance forward) which is listed in E6 and so on to the end of the list.

    In column C5 & D5 I have the following item description: Soap Bar Lime 6oz / Package 2
    In column E5 I have the existing previous inventory quantity listed in number of pieces eg. 56
    In column F5 I enter/add the new products manufactured in number of pieces eg. 44
    In column G5 I enter/subtract the quantity sold in number of pieces eg. 22
    In column H5 I need to get the updated quantity of inventory which in this case would be 78
    This could look like this in H5 cell: =sum(E5+F5)-G5 The problem is that F5 and G5 are constantly updated with production and sales, and this formula only allows me to replace the numbers.
    In column J5 I need to get a formula for the year to date products manufactured which was entered in column F and added each time a new product was manufactured eg. 326 (pieces of soap)
    I need to keep these 2 running balances on column H (curent inventory) and in column J (year to date manufactured) while I can put new/fresh data in columns F and G.

    Thanks
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1