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
Bookmarks