Results 1 to 12 of 12
Thread: Running Sum

20030205, 15:14 #1
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: consolidating data (Excel 2000)
Linda,
I think you might want the 'get external data' tool, which has options to add to, overwrite or whatever else to the 'copied' data from the original source, if I remeber correctly.
Can't be more specific without knowing more.

20030205, 19:10 #2
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Running Sum
Maybe what I am looking for is how to do a running sum. I want the data in one column (where the data changes ) to add to another column in another speadsheet and that number to increase as the number changes on the first spreadsheet. I tried creating a column for the calculation but when the data that was to be added changed, it only kept adding to the original number so If I had 30 and say the other spreadsheet said 2 then I get 32  that was fine except when the number changed to say 3, the total was 33 and not 35 like I want it to be. Am I asking for the impossible?

20030205, 19:37 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Running Sum
I'm unclear what you want or the layout of the data. Where column A in Sheet 1 is the data you want a running total on will something like
=SUM(Sheet!1A:A
work?John ... I float in liquid gardens
UTC 7ąDS

20030205, 19:57 #4
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
To make it simple  There is a spreadsheet with numbers in column A (someone else maintains this spreadsheet). These numbers change. I created the same spreadsheet. What I want to do is whenever a number changes on the first spreadsheet, I want it to add into the number of the second spreadsheet. Say the first spreadsheet has 4. The other spreadsheet has 4. The number changes on the first spreadsheet to 2. The second spreadsheet should say 6. The number changes on the first spreadsheet to 1. The second spreadsheet should say 7and so on and so forth. Does this make sense? Is this possible?

20030205, 20:57 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Running Sum
So the number on the first is spreadsheet is entered in the exact same cell every time? And you want to take that number, every time it is changed, and enter it to a new row on another sheet (or another workbook)?
John ... I float in liquid gardens
UTC 7ąDS

20030205, 22:21 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
If you place the code below in the Worksheet Change event routine for the sheet where the number will be entered in column A, the routine will keep a running sum in column A of Sheet2.
<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then Exit Sub
For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
If IsNumeric(oCell.Value) Then
Worksheets("Sheet2").Range(oCell.Address).Value = Worksheets("Sheet2").Range(oCell.Address).Value + _
oCell.Value
End If
Next oCell
End Sub
</pre>
Legare Coleman

20030206, 10:48 #7
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
Sorry  I know how to create macros but I am not sure how to place the code in the Worksheet Change event routine for the sheet. Could you explain what I have to do. The numbers in the first worksheet is in another workbook.

20030206, 14:37 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
The code I sent will keep the running totals in a sheet named Sheet2 in the same workbook as the sheet with the numbers. If you need to have the totals in another workbook, there are two choices. You can either use a formula to refernece the totals in the workbook with the numbers, or the routine will have to be modified to put the totals in the second workbook. To do that, I would need to know the name of the second workbook, and you would have to make sure that the second workbook was always opened first.
To put the code in the worksheet change event routine, do the following:
1 Open the workbook containing the numbers and select the worksheet.
2 Right click on the tab for the worksheet and select "View code" from the pop up menu.
3 In the left hand drop down list at the top of the VBE window, drop down the list and select "Worksheet".
4 Drop down the right hand drop down list and select "Change" from the list.
5 Paste my code to replace the dummy Worksheet_Change event routine that the VBE displays.Legare Coleman

20030206, 15:59 #9
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
I followed your instructions and I enterd the code for worksheet 1 but when I put a number in column A of worksheet 1 and select worksheet 2 to see if the number is there  it isn't. I am also getting an error and it highlights this part of the code:
If Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then Exit Sub

20030206, 20:54 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
Without knowing what error you are getting, and without seeing the rest of the code (in particular the Sub statement), it is a bit difficult to guess what the problem might be. I have attached a workbook with the code included in Sheet1 that works.
Legare Coleman

20030207, 13:38 #11
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
That worked. I was getting the error because after I entered a number in column A, I was selecting sheet 2 without hitting enter after putting the number in sheet 1. This is great. I expanded the range from A:A to include columns B, C, D and E. Entering the data manually in sheet 1 works fine. Is there any way to automatically bring numbers into sheet 1 from another workbook. I tried consolidating and the number on sheet 2 changes the first time but when the number changes on sheet one (using consolidation) the number does not increase in sheet 2. I tried putting = and going to the other workbook for the data in say A1. When the other workbook's number changed, Sheet1 changed but sheet2 did not add it in. Is there any way sheet 1's numbers can be brought in from another workbook and sheet 2 will increase accordingly? Thanks for your help. If the solution is too complicated, what you gave me so far is great. Thanks again.

20030207, 18:35 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Running Sum
If you copy the numbers from the other workbook and paste them into Sheet1, then they should be added to Sheet2. Using formulas to reference the other workbook definitely will not work, and I think that using consolidation would be just like using a formula and also would not work.
Legare Coleman