Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
having a running total
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
having a running total
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 B3:D3 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 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kathy Flynn" <Kathy wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
having a running total
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 "Kathy Flynn" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
having a running total
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" wrote: 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 B3:D3 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 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kathy Flynn" <Kathy wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
having a running total
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). --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kathy Flynn" wrote in message ... 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" wrote: 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 B3:D3 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 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kathy Flynn" <Kathy wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Running Total for each worksheet | Excel Worksheet Functions | |||
Mileage Claim Formula | New Users to Excel | |||
Running total.... | Excel Discussion (Misc queries) | |||
running total by date (not sorted by date though...) | Excel Worksheet Functions | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) |