increasing and decreasing a value in a cell

Hi there, first time user here.

I am looking for some help with a personal worksheet I am creating to control stock of medication by usage. What I need help on is that I have an initial value in a cell (stock level) and I need to add to it the value of another cell (supply) in such a way that stock level now reflects the new value and increases as a new value is entered in the supply field and decreases based on a value (daily usage) in another cell. The spreadsheet has to hold the new stock level as the current value each time the document is opened and amended.

ie:
B3 (stock level) initial value is 20.
B4 (supply) is 4 value added to B3
B3 (stock level) value now 24
B5 (daily usage) -3 value deducted from B3
B3 (stock level) now 21
B4 (supply) changed to 6 value added to B3
B3 (stock level) now 29


I don't know enough about advanced formulas or scripting to get the worksheet to do what I need it to do. It has to be able to run in excel as I need to carry the worksheet around in a PDA.

If anyone can decipher what I am looking for and can advise a solution it would be VERY appreciated.

Hi,

There is something I do not understand ...
the final value is either 23 or 27 ...
If it is 29 ... can you explain how ?

Carim

Originally Posted by Carim

Hi,

There is something I do not understand ...
the final value is either 23 or 27 ...
If it is 29 ... can you explain how ?

Carim

Oh dear! my very first post and I can't even add 6 to 21...

Yes. 27 would be the final result in this example.
(covers head in shame).

The question is whether you can input your values in distinct cells, or if, for some reason, there is a substitution taking place ...in your example is 6 replacing 4 or is 6 added to another cell ... ?
As you can imagine, the solution will not be the same ...

Carim

For what I want, 6 will replace 4.

This cell will be where I enter how much we bought of a specific item. It will then be added to the current stock level.

The other cell being used will be how much we used of the specific item and will be deducted from the current stock level.

The third cell being the stock level.

Well I would have Current Stock in cell C3 separate from initial stock in B3,
and following event macro in worksheet module :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As Variant, NewVal As Variant
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set Target = Range("B4")
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Range("C3").Value = Range("B3").Value + Range("B5").Value + NewVal + OldVal
Target.Value = NewVal
Application.EnableEvents = True
End Sub

HTH
Carim

aha Carim,

I see where you went with this.... I will try this as soon as I can. Many thanks for the help and good job decyphering my initial post!

regards,
CWAL