# Thread: running totals (Excel 2000)

1. ## 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. ## 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.

Thank you.

4. ## 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. ## 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. ## Re: running totals (Excel 2000)

Yes, thank you.

7. ## 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. ## Re: running totals (Excel 2000)

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

9. ## 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. ## 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. ## 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. ## Re: running totals (Excel 2000)

Excellent knowledge you just taught me.

13. ## 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. ## 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. ## 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
•