Greetings Friends,
I have been doing a bit of coding in VBA on some reports for the place I work.
I am already 90% through and up with this seemingly small challenge.
I need to display only one of the column value (count) and yet show the sum of all column values.
In other words I need to have the columns hidden for the other columns but total should show that of all.
So I only want certain columns hidden.
However, when columns are hidden on the interface, pivot table totals only what is displayed.
But in effect the pivot table object still has all the hidden data available to it, doesn't it?
It should be able to calculate the total of all columns and display it maybe as a separate calculated column via VBA or something in that line.
Do you think there could be such a possibility?
Could anyone explain if this can be done via VBA.
Greatly appreciate your help.
Sample data as below:
I need to display column labeled '2' only and hide all other possibilities and yet keep grand total values the same.
[TABLE="width: 387"]
Calls Handled
Column Labels
Row Labels
1
2
3
4
Grand Total
ALA-ARA-SER
340
831
11
1182
ALA-ENG-SER
80
171
2
253
ALQ-ARA-SER
41
119
160
ALQ-ENG-SER
179
483
662
CEN-ARA-SER
866
2658
1
80
3605
CEN-ENG-SER
861
3595
50
4506
DIP-ENG-SER
5
2
7
FUJ-ARA-SER
29
55
84
FUJ-ENG-SER
9
7
16
GAR-ARA-SER
232
626
9
867
GAR-ENG-SER
319
901
1
1221
MUS-ARA-SER
783
1968
35
2786
MUS-ENG-SER
726
2016
31
2773
QAB-ARA-SER
65
145
1
211
QAB-ENG-SER
209
412
621
RAK-ARA-SER
212
358
6
576
RAK-ENG-SER
61
107
168
SHJ-ARA-SER
287
696
6
989
SHJ-ENG-SER
246
570
816
SZR-ARA-SER
498
1459
32
1989
SZR-ENG-SER
1452
4481
4
5937
Grand Total
7500
21660
1
268
29429
[/TABLE]