Accumulating Multiple Inputs From a Single Cell

[don2712]

I am running XL 2007 and what I want to do is to be able to put a series of
different numbers into one individual cell and have them show as an
accumulated total in another.
The idea is to be able to produce a score system for a game with up to 6
people playing without having having to have long columns of input for each
player.
Each player would have a a single cell below their name to input their
scores during the game and adjacent to that another cell would display the
running total.
Any help would be appreciated.
Thank you

 

[Don Guillett]

Right click sheet tab>view code>insert this. Now when you change a cell in
col b, col c will increase. Put the names in col A.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If Intersect(target, Range("b2:b22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
target.Offset(, 1) = target.Value + target.Offset(, 1)
Application.EnableEvents = True
End Sub

 

[don2712]

Thank you for your reply, I have never used the developer before so I'm
afraid I still have a couple of queries.
I typed the routine in exactly as it is here but line 4 starting
'target.Offset' seems to have a problem as it is being highlighted in yellow
however, as far as I can see everything seems to have been entered correctly.
Secondly, I do not know how to activate the routine once it has been
accepted by the developer.

 

[Don Guillett]

All you should have had to do is follow my instructions to right click the
SHEET tab>view code>COPY/paste this instead of typing it in. Activating the
routine is AUTOMATIC when you enter a number in cells b2:b22 then number in
the column to the right will increase by the number entered. Pretty
simple....................

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If Intersect(target, Range("b2:b22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
target.Offset(, 1) = target.Value + target.Offset(, 1)
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Can be done per Don's code.

I would point out that there is no way of error checking this type of
operation.

There is no "paper trail" to show you when/if a mistake is made in data
entry.

Excel sheet has many cells............use them to input scores for each
player.


Gord Dibben MS Excel MVP