Incrementing within a Cell

[Martha W. Stone]

I have a friend who has a worksheet where she is tracking the number of
people that respond to an ad in different newspapers. She wants to be able
to type "1" in a cell next to a paper and then when she goes back for the
next person's response, type a "1" in the cell again and have the total come
up to "2". I'm sure that she will have to type in one cell and have the
total come up in another. Is there a way to do some sort of incrementing?
Maybe a formula that can take the current number in the cell and add the
number that is typed in? I thought I was pretty Excel-literate, but this is
over my head!

Martha

 

[Frank Kabel]

Hi Martha
can only be done with VBA. Have a look at the folowing site for some
example code to create such an accumulator:
http://www.mcgimpsey.com/excel/accumulator.html

 

[Andy B]

Martha

Have a look at a spinner on the Forms Toolbar.
This can be linked to a cell, and increment up or down with a click.

Andy.

 

[Bob Phillips]

Hi Martha,

You can easily do it with VBA.

Put this code in the worksheet code module (right-click the sheet tab,
select View Code, and past it in)


Dim oldval

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A10") Is Nothing Then
Target.Value = oldval + 1
oldval = oldval + 1
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

Martha

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can be done,
not as a good solution. You would be much better off to have another column so
you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check Iterations
and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in the
Application D4 will update even if no new number is entered in C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no "paper
trail" to follow. Any mistake in entering a new number in C4 cannot be
corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4 and
Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL which does
what you want without the re-calculation problem, but again there is no "paper
trail" for back-checking in case of errors in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html

I believe Frank K. has previously given you J.E.'s site URL

Gord Dibben Excel MVP