Pivot Table Formula Disaster

[MikeyFB]

Hello!

Please bare with my images, its a word document, im at work and therefore have no otehr option. The problem is when i enter my YTD formula the table duplicates all the sub categories into all the categories, which is unwanted.

Please help!

http://www.megaupload.com/?d=I2RH593Q

 

[MikeyFB]

Any ideas?

 

[MikeyFB]

An IF formula maybe? that omitts the 0's?

 

[Andrew Poulsom]

The only solution I know requires VBA - source Debra Dalgleish in:

http://www.pcreview.co.uk/forums/thread-1776326.php

Sub HidePivotZeroRows()
'   hide worksheet rows that contain all zeros
    Dim rng As Range
    For Each rng In ActiveSheet _
        .PivotTables(1).DataBodyRange.Rows
        If Application.Sum(rng) = 0 Then
            rng.EntireRow.Hidden = True
        Else
'           unhide any previously hidden rows
            rng.EntireRow.Hidden = False
        End If
    Next rng
End Sub

Sub UnhidePivotRows()
'   unhide all rows
    Dim rng As Range
    For Each rng In ActiveSheet _
        .PivotTables(1).DataBodyRange.Rows
        rng.EntireRow.Hidden = False
    Next rng
End Sub

 

[MikeyFB]

Ok, so how would I go about entering that code, Im a complete amatur!

 

[Andrew Poulsom]

Press Alt+F11 to go to the Visual Basic Editor. Click your workbook in the Project window on the left and choose Insert|Module from the menu. Paste the code I posted in the window on the right. Press Alt+F11 to return to your workbook.

From the menu choose Tools|Macro|Macros, choose the one you want and click Run.

 

[MikeyFB]

Wow! Thank You so much! I am very grateful!