Results 1 to 12 of 12

Thread: Running Sum

  1. #1
    2 Star Lounger
    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.

  2. #2
    5 Star Lounger
    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?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  4. #4
    5 Star Lounger
    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?

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  6. #6
    Uranium Lounger
    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

  7. #7
    5 Star Lounger
    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.

  8. #8
    Uranium Lounger
    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

  9. #9
    5 Star Lounger
    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

  10. #10
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

  11. #11
    5 Star Lounger
    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.

  12. #12
    Uranium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •