Is It Possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

documike - 29 Aug 2007 18:00 GMT

Is it possible to have a cell that's accumulative...in other words, if I
have a cell that has a value of 4 and I want to add 2 to it by typing in the
"2" and have it now have a value of 6? I'm trying to avoid having a list
that adds and then link the total cell. I've never done this and don't
recall ever seeing it done.

Reply to this Message

Bob I - 29 Aug 2007 18:36 GMT

press F2 and then key in +2 in the cell following the current contents

> Is it possible to have a cell that's accumulative...in other words, if I
> have a cell that has a value of 4 and I want to add 2 to it by typing in the
> "2" and have it now have a value of 6? I'm trying to avoid having a list
> that adds and then link the total cell. I've never done this and don't
> recall ever seeing it done.

Reply to this Message

documike - 29 Aug 2007 19:50 GMT

I tried this...however it does not total the amounts, only 4 + 2 etc.
instead of 6
I guess I could manually do a =SUM( at the end of entries but I'm trying to
automate as much as possible.

> press F2 and then key in +2 in the cell following the current contents
>
[quoted text clipped - 3 lines]
>> list that adds and then link the total cell. I've never done this and
>> don't recall ever seeing it done.

Reply to this Message

Bob I - 29 Aug 2007 21:08 GMT

Then it is seeing it as text. Put a "=" in front
=4+2

> I tried this...however it does not total the amounts, only 4 + 2 etc.
> instead of 6
[quoted text clipped - 8 lines]
>>>list that adds and then link the total cell. I've never done this and
>>>don't recall ever seeing it done.

Reply to this Message

Jim Thomlinson - 29 Aug 2007 18:48 GMT

While I personally am not a fan of doing this as there is no audit trail to
determine how the number was arrived at here is some code to do it for you...
right click the sheet tab where you want the accumulator and paste this code
(it works for cell A1)...

Private dblAccumulator As Double

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler

Application.EnableEvents = False
If Target.Address = "$A$1" Then Target.Value = Target.Value +
dblAccumulator
Errorhandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then dblAccumulator = Target.Value
End Sub

Signature

HTH...

Jim Thomlinson

Reply to this Message

documike - 29 Aug 2007 19:55 GMT

I actually have several cells that I need to do this...can I simply use
something like this?:

If Target.Address = "$A$1","$C$14","$D$3 Then Target.Value = Target.Value +

> While I personally am not a fan of doing this as there is no audit trail
> to
[quoted text clipped - 27 lines]
>> that adds and then link the total cell. I've never done this and don't
>> recall ever seeing it done.

Reply to this Message

Jim Thomlinson - 29 Aug 2007 20:44 GMT

A little trickier than that...

Private dblAccumulator As Double

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler

Application.EnableEvents = False
Select Case Target.Address
Case "$A$1", "$B$1", "$C$1"
Target.Value = Target.Value + dblAccumulator
End Select
Errorhandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1", "$B$1", "$C$1"
dblAccumulator = Target.Value
End Select
End Sub

Signature

HTH...

Jim Thomlinson

> I actually have several cells that I need to do this...can I simply use
> something like this?:
[quoted text clipped - 32 lines]
> >> that adds and then link the total cell. I've never done this and don't
> >> recall ever seeing it done.

Reply to this Message

Rate this thread: