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