Pivot table, how do you exclude counting cells with formulas as a
 

The set-up; (Example only....scenario is much larger) I have a pivot the is
summing the voting results for the state of PA in in 3-categories, appearing
in cells B1 through B3.
Cells A1 through A3 have the 3 categories Democratic, Republican
&Independant.
The problem: The Independant has a null values for its CALCULATED
results(the cell is blank). However, The pivot is showing the name
INDEPENDANT in the pivot.
How can I have the pivot not to show indenpendant without constantly using
the drop-down box to un-check the unwanted value. (again, my actual scenrio
has over 500 constantly changing values. Let me know. Thanks!

Right click on the field, choose field settings, clear the check box for
"Show items with no data".


"Greg Bobak" wrote in message
...
The set-up; (Example only....scenario is much larger) I have a pivot the
is
summing the voting results for the state of PA in in 3-categories,
appearing
in cells B1 through B3.
Cells A1 through A3 have the 3 categories Democratic, Republican
&Independant.
The problem: The Independant has a null values for its CALCULATED
results(the cell is blank). However, The pivot is showing the name
INDEPENDANT in the pivot.
How can I have the pivot not to show indenpendant without constantly using
the drop-down box to un-check the unwanted value. (again, my actual
scenrio
has over 500 constantly changing values. Let me know. Thanks!

You could use code to hide the calculated items that are zero. For example:

'=====================================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub

'===================================


Greg Bobak wrote:
The set-up; (Example only....scenario is much larger) I have a pivot the is
summing the voting results for the state of PA in in 3-categories, appearing
in cells B1 through B3.
Cells A1 through A3 have the 3 categories Democratic, Republican
&Independant.
The problem: The Independant has a null values for its CALCULATED
results(the cell is blank). However, The pivot is showing the name
INDEPENDANT in the pivot.
How can I have the pivot not to show indenpendant without constantly using
the drop-down box to un-check the unwanted value. (again, my actual scenrio
has over 500 constantly changing values. Let me know. Thanks!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Carlos,
Unfortunately this won't work for me. The check box is already cleared. My
scenario is that the pivot table is picking-up cells in the range that
contain only the formula...the result of the formula's calculation is
currently null. The pivot table IS showing zero but, I don't want to see it
at all in the pivot. Any other ideas?

"CarlosAntenna" wrote:

Right click on the field, choose field settings, clear the check box for
"Show items with no data".


"Greg Bobak" wrote in message
...
The set-up; (Example only....scenario is much larger) I have a pivot the
is
summing the voting results for the state of PA in in 3-categories,
appearing
in cells B1 through B3.
Cells A1 through A3 have the 3 categories Democratic, Republican
&Independant.
The problem: The Independant has a null values for its CALCULATED
results(the cell is blank). However, The pivot is showing the name
INDEPENDANT in the pivot.
How can I have the pivot not to show indenpendant without constantly using
the drop-down box to un-check the unwanted value. (again, my actual
scenrio
has over 500 constantly changing values. Let me know. Thanks!

Hi Debra,
I don't know where I would begin to use code. I'm not proficient in that
area yet. Any other ideas?

"Debra Dalgleish" wrote:

You could use code to hide the calculated items that are zero. For example:

'=====================================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub

'===================================


Greg Bobak wrote:
The set-up; (Example only....scenario is much larger) I have a pivot the is
summing the voting results for the state of PA in in 3-categories, appearing
in cells B1 through B3.
Cells A1 through A3 have the 3 categories Democratic, Republican
&Independant.
The problem: The Independant has a null values for its CALCULATED
results(the cell is blank). However, The pivot is showing the name
INDEPENDANT in the pivot.
How can I have the pivot not to show indenpendant without constantly using
the drop-down box to un-check the unwanted value. (again, my actual scenrio
has over 500 constantly changing values. Let me know. Thanks!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html