replace a formula by a value but further +/- is still need calculate

[jimmy]

Hi all,

My excel file is help for keeping goods amount, anyone who take goods is
need to update the file. A cell is contain each item minimum value and if
that item reach the minimum level we have to order the related items.

Two columes are for +/- goods, one cell (say A1) is a simple formula that
show the balance by calculating the +/- of the two colume and one cell
contains the minimum value.

My problem is, we sometimes take a stock checking, the value after checking
basically should replace the value in cell A1, but it is not a practicable
way since if replace the cell A1 to the after checking value , further +/-
cannot be calculated. Any idea? Thanks

 

[Tom Ogilvy]

use a database.

When you received items, put that information on a row
when you remove items, put that information on the next row
Continue down the sheet.
(first rows will indicate starting inventory)

Each row should include the date, information that identifies the item,
information that indicates why the change. Indicate additions with positive
numbers and removals with negative numbers.

Then use
=Sumif(Data!A:A,Item,DataC:C)

with the item name in column A and the quantity of the transaction in Column
C (as an example)

This sum will give you the current inventory.

This will also give you an audit.