apologies for my tardiness, been a bit preoccupied with the job hunt...
regardless, try the attached.
It's just an on-change macro, ie when a value on the sheet changes it fires off. It uses a couple of 'helper' values - the stock column (B) is duplicated in column D and used to check for changes in stock levels, and there are subtotals in cells B1 and D1.
- If the value in the stock column goes up, the corresponding check value in column D is amended accordingly.
- If the value in the stock column goes down, the difference between the new value and the check value is added to the sales column (E), and the check value is amended to match the new stock value.
in practice both row 1 (with the subtotals) and the check column would be hidden as they don't add anything useful to the user (and any user changes to those values would result in corrupted values)
its not terribly pretty, but it works OK for a first cut
appreciate any feedback you might have