increasing and decreasing a value in a cell

[cwal]

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.

 

[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

 

[cwal]

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).

 

[Carim]

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

 

[cwal]

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.

 

[Carim]

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

 

[cwal]

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