having a running total

[Guest]

I want to have a columns with totals which I can update when I enter info.
Example: Envelope Balance Added value Running Total
1 £2.00 3.00 5.00
I want to be able to keep inputting different values into 'Added Value' as I
sell things from that envelope. Anyone know how I can do this?
Thanks.
Kathy

 

[David McRitchie]

Hi Kathy,
I don't know why you would show a Balance
and a Running total. But as I understand your
question.

B2: 2.00
C2: <entry>
D2: =B2+C2

B3: =OFFSET(D2,-1,-)
C3: <entry>
D3: =OFFSET(D2,-1,0)+C3

Use the fill handle to copy B33 downward (if C3 is empty)
http://www.mvps.org/dmcritchie/excel/fillhand.htm
or use an automatic entry with an event macro as below

Right click on the sheet tab, View Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module.

If Target.Column <> 3 Then Exit Sub
If Target.row < 3 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Dim R As Long
R = Target.row
Target.Offset(0, -1).Formula = _
"=OFFSET(" & Target.Offset(0, 1).Address(0, 0) _
& ",-1,0)"
Target.Offset(0, 1).Formula = _
"=OFFSET(" & Target.Offset(0, 1).Address(0, 0) _
& ",-1,0) + " & Target.Address(0, 0)
ErrHandler:
Application.EnableEvents = True
End Sub

Read about Event macros at
http://www.mvps.org/dmcritchie/excel/event.htm
Read about inserting rows and OFFSET at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Guest]

column A columnB
row1 Evelope I
row2 Total £5.00 formula Sum(b2:b32)
row3 item 1 £2.00
row4 item 2 £3.00
row5 item 3
row6 item 4
row7 item 5
row8 item 6
row9 item 7
row10 item 8
row11 item 9
row12 item 10
row13 item 11
row14 item 12
row15 item 13
row16 item 14
row17 item 15
row18 item 16
row19 item 17
row20 item 18
row21 item 19
row22 item 20
row23 item 21
row24 item 22
row25 item 23
row26 item 24
row27 item 25
row28 item 26
row29 item 27
row30 item 28
row31 item 29
row32 item 30

 

[Guest]

Hi David,
I don't think I explained myself very clearly or maybe I just can't figure
out your answer. I tried the OFFSET thing but it didn't seem to work. What I
have is a series of envelopes each with about 100 postcards I'm selling. When
I sell a postcard I want to update the data for that envelope that I have
sold a card and how much for so that I know how many cards I have sold from
that envelope for a total of how much. I was using one row for each envelope
with the columns for 'no. of cards', 'cards sold', 'amount added' and
'running total'. Perhaps you can suggest a way??

 

[David McRitchie]

The example formula were incorrect
though the macro does what I understood
though I don't think it is want you want because
now you indicate a variable number of postcards
as being the items sold from an envelope.

B2: 2.00
C2:
D2: =B2+C2

B3: =OFFSET(D3,-1,0) instead of =D2
C3:
D3: =OFFSET(D3,-1,0)+C3 instead of =D2+C3

as you enter values in the C column
starting in C3 the other formulas on each
side of the entered value would be filled in
if you install the event macro.

I can't tell from one row what you want, looks
to me like you are only entering cash amount pounds in
the middle and totaling the cash received.

If you are trying to always enter on one row
to cumulatively add to what is already on the
same row that would not be a good thing to
do (no audit trail) and I don't do them.

As long as the intent is enter each sale on the
row below the previous sale there would be no
problem in modifying what I sent you, so email me
an example with several sales in it so I can see
what you want. (simply reply to me, rather than to group).