Suppose you have a table with a number of columns.
And suppose you wanted to sum the Num6 column. You’d select a cell in that column and use Alt+JT+T to add the totals row.
But Excel always sums the last column (that it’s able to) when it should really sum the one you’re on. That’s an easy enough fix
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Private Sub Worksheet_Change(ByVal Target As Range) Dim lo As ListObject 'There's a lot that can go wrong here, so we'll just ignore everything On Error Resume Next 'Loop through every table on the sheet - but there should only be one IMO For Each lo In Me.ListObjects 'If the range you're changing is exactly the same as the total row, 'you're probably adding the total row. If there is no total row 'you get an error - thus the on error above If Target.Address = lo.TotalsRowRange.Address Then 'If you selected a cell in the table, that's the column you want to sum If Not Intersect(ActiveCell, lo.Range) Is Nothing Then lo.ListColumns(ActiveCell.Column - lo.DataBodyRange.Column + 1).TotalsCalculation = xlTotalsCalculationSum End If Exit For End If Next lo End Sub |
That won’t stop the last column from also being summed, but it’s easier to destroy than create. I’ll let that happen and go delete it if I don’t want it. But we’re not done yet. The craziness is just starting. By the way, I’m using Office 2010. If they fixed any of this stuff in later versions, bully for them, but I’m still not upgrading.
The formula to sum Num6 is: =SUBTOTAL(109,[Num6]). If I copy and paste that to Num5
The formula stays the same. That’s consistent with how copying formulas that use Structured Table Referencing (STR), but it really stinks in this case. If I want to sum all of the columns, I have to go select the appropriate aggregate function from the list. I could change the STR into normal cell references, but then I lose the table goodness.
One option is to select a cell in Num5 and press Alt+JT+T+Alt+JT+T. Turning the totals row off and then on again sums up for that column by calling the code I wrote above. The fact that this works means I should have check to see if there was already an aggregate in the cell before I overwrote it. That is, if I had a count formula in there and turned the total row off and then on again, I’d kill that row. If I fix that, this workaround doesn’t work. But it’s the right thing to do.
|
1 2 3 |
If IsEmpty(Intersect(lo.TotalsRowRange, ActiveCell.EntireColumn).Value) Then lo.ListColumns(ActiveCell.Column - lo.DataBodyRange.Column + 1).TotalsCalculation = xlTotalsCalculationSum End If |
Well that doesn’t work. If I have =SUBTOTAL(103,[Num5]) in the cell and turn on and off the total row, it changes to =SUBTOTAL(109,[Num5]).
I think what’s happening here is that Excel tables have a good memory. Once I change the aggregate to sum, that becomes the default for that column. When you show totals for the first time, the event sequence is (with the Target in parentheses)
- Add the total row (A5:H5)
- Add the total label (A5)
- Aggregate the last column (H5)
When I re-show the total row after having changed the aggregate, the event code changes the default aggregate, and when the table restores the formula it’s using the that aggregate. Assume I’m in Num7 when I show totals the first time. Then I move to Num5, add a count, and hide and show totals.
| Event | Target | Num5 Default Aggregate |
|---|---|---|
| Show Totals 1st time | A5:H5 | SUM |
| Add label | A5 | SUM |
| Aggregate Num7 (automatic) | H5 | SUM |
| Add Count to Num5 (manual) | F5 | COUNT |
| Hide Totals | A5:H5 | COUNT |
| Show Totals 2nd time | A5:H5 | COUNT |
| Change Event | F5 | SUM |
| Add label | A5 | SUM |
| Aggregate Num5 (automatic) | F5 | SUM |
| Aggregate Num7 (automatic) | H5 | SUM |
The second-to-last change is Excel restoring the Num5 total from the last time. It doesn’t restore it to what it was, it restores it to whatever the last aggregate used was. Since I interjected some code between the adding of the row and the restoration of the aggregate, I changed the default for that column. At least I think that’s what happening.
“I could change the STR into normal cell references, but then I lose the table goodness.” …
Or you could use your mouse and drag the formula over. More efficient than writing a bunch of code. Then we could have an International Structured Table Mouse Drag Day!
I second Doug ;)