Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    running totals (Excel 2000)

    I have a need for a formula that sums the current quarter plus the previous 3 quarters. Example: This is Year 2005, Qtr 1. I need the formula to add Quarter 1 of 2005, plus Quarter 4 of 2004, plus Quarter 3 of 2004, plus Quarter 2 of 2004. If the year is 2005, Quarter 2. I need the formula to add that quarter plus Quarter 1 of 2005, plus Quarter 4 of 2004, plus Quarter 3 of 2004. I have attached a sample. The formula needs to be on sheet, Rolling Quarters, cell B71.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    The only way I can tbink of requires a thorough reshuffling of the data that will throw off other formulas. I have added formulas for the rolling sum in the sheet named Cindy, at the bottom; they can be hidden if you like. The formulas in B71:B74 on the Rolling Quarters sheet look up the value according to the selected year and quarter.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    Thank you.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    I need to restate this. In the Running Totals sheet, the user will use the combo boxes to pick the quarter to view. Down in cell F71, I need a formula to lookup the match on the Notes sheet. If there is a match to the year and the quarter, then use the formula listed in column F. The formula needs the respective sheet inserted. Example: On the Notes sheet, Cell F2, the formula is written as '=AB71+Y71+X71+W71 and it should '=AOC!AB71+AOC!Y71+AOC!X71+AOC!W71. Can you help?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    Perhaps the Excel gurus can, but I don't know how to do this with formulas only. It would be possible to create the formulas using VBA code each time the user selects a year or quarter. Is that acceptable?

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    Yes, thank you.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    The workbook in the attached zip file adjusts the formulas in F71 and G71 when the user selects a year or quarter.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    That was brilliant. How did you link the combo box to the formulas?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    I didn't really link them. The On Change event code for the combo boxes calls a macro that sets the formulas. Activate the Visual Basic editor to see the code (Alt+F11). The macro is in Module1, and the On Change code is in the Rolling Quarters module.

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    When I copied both the macro and the combo boxes for the rolling quarters to the actual spreadsheet, the the combo box works, but the cells do not update the values like they do in the prototype. I checked properties, link cell and list field and they all go to the correct sheet and range. any idea why the cells don't update?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    Did you also copy the cboYear_Change and cboQuarter_Change event procedures from the Rolling Quarters worksheet module to the corresponding worksheet module in your own workbook? Those event procedures are necessary, and they MUST be in the module belonging to the worksheet containing the combo boxes.

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    Excellent knowledge you just taught me.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running totals (Excel 2000)

    I need to make a change to the macro. In this statement:
    For intCol = 6 To 7
    I need to also include column B, so I changed the code to read:
    For intCol = 2, 6 To 7
    It didn't work. How should I have included the extra column in the code?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: running totals (Excel 2000)

    A For statement doesn't work that way - look it up in the VBA help. You'll have to treat column B separately.

    intCol = 2
    strSheet = Worksheets("Rolling Quarters").Cells(69, intCol)
    strFormula2 = Replace(strFormula1, "+", "+'" & strSheet & "'!")
    Worksheets("Rolling Quarters").Cells(71, intCol).Formula = strFormula2

    For intCol = 6 To 7
    strSheet = Worksheets("Rolling Quarters").Cells(69, intCol)
    strFormula2 = Replace(strFormula1, "+", "+'" & strSheet & "'!")
    Worksheets("Rolling Quarters").Cells(71, intCol).Formula = strFormula2
    Next intCol

    If you need to do this for a lot of indivual columns, the code could be split off into a function or procedure.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: running totals (Excel 2000)

    Another option is an array (untested aircode)

    dim vArray as variant
    varray = array(2,6,7)

    For intCol = lbound(varray) to ubound(varray)
    strSheet = Worksheets("Rolling Quarters").Cells(69, intCol)
    strFormula2 = Replace(strFormula1, "+", "+'" & strSheet & "'!")
    Worksheets("Rolling Quarters").Cells(71, intCol).Formula = strFormula2
    Next intCol


    With this way you can use any columns as desired without separate loops.

    Steve

Posting Permissions

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