Dear All,
Please can somebody help with a problem that I have spent the last two days trying to solve.
Scouring the net, I have managed to find lots of "very similar" questions but unfortunately none offered any answers!
I have cut down my problem to its simplest form - in the real world my spreadsheet is significantly more complex but the attached spreadsheet demonstrates it nicely!
The data is straightforward. PT1 shows the data without a calculated item whilst PT2 is the same PT but with the addition of a calculated item for GP (i.e Sales - COS). Unfortunately adding this has resulting in the PT growing and adding "ilogical" combinations of data that didn't exist in the source data.
In my current project I have "cheated" by using a helper column and filtering on this using the Worksheet_PivotTableUpdate event to hide the unwanted rows but surely there must be a way of removing them "properly".
Another problem, and I guess this is just the way it is, is that my PT takes ages to calculate presumably becuase it is calculating this formula for every combination of values?
Please can somebody assist / advise what code I need to attached to the button on my spreadsheet to make the items disappear - preferably not just to "delete the rows" but make it disappear from the PT?
Many thanks in anticipation,
regards,
pete