Adding to the same cell

[thetigman]

Hi there,

I only started using excell a few weeks ago in my ne
job, I am doing a costing sheet that formulates eveything to a gran
total at the bottom.

I dont put in the cash amounts all i do is enter th
square meters (sq/m) of materials used, so my problem is as i go
through my jobs for the week, i might put in-

2.5 sq/m of 1.0 m/m material

then

6.8 sq/m of 1.5 m/m material

now i have to go back to the 1.0m/m material to addon

5.6 sq/m of material.

Now is there a way to add the 5.6 sq/m to the 2.5 sq/m ?

Sorry if im confusing

Anybody who helps thanks

 

[Bob Phillips]

Hi,

Here is some VBA code that will do what you want for the cells A1:A10. To
enter it, right-click on the sheet tab, select View code from the menu, and
paste the code in

Private Sub Worksheet_Change(ByVal Target As Range)

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

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldval = Target.Value
End Sub


--

HTH

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

 

[Don Guillett]

try this. Right click sheet tab>view code>copy/paste this. Works on cell A5

Option Explicit
Dim oldvalue As Double

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

 

[Gord Dibben]

thetig

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


Gord Dibben Excel MVP

 

[Ken Wright]

Need to be careful here. Can you really buy 5.6 sq/m or will the system round
that up to 6.0 sq/m? If it does then you have to cost each one separately. If
it doesn't then you can just amend the cell with the 5.6 in it, so for example
you might start with 5.6 in the cell and then have to add 1.2, so add an = sign
at the start of the cell and then tack on the new number, eg:-

5.6 (No need for an = if number is on it's own)

=5.6+1.2 and so on (Need an = for calculations)

=5.6+12.5+3.4+5.6+4.5 etc

This way you see all the individual entries until you are done

 

[thetigman]

WOW thanks for all the replies, ill have a look & see how i go.

Thanks again