Pivot Table Data Filter Options Disappear

  • Thread starter Thread starter Pepikins
  • Start date Start date
P

Pepikins

I have created a pivot table with people's names in the ROW, year group
and academic year in the COLUMN and weight/height in the DATA area.
Below is a copy of the pivot table...

Year Group Academic Year
4 5
NAME Data 2004 2005
NAME 1 Height 1.44 1.44
Weight 51.00 51.00
NAME 2 Height 1.44 1.44
Weight 51.00 51.00


The DATA field has a drop down box to filter weight/height but if I use
the filter to only show Height, I cannot get weight back (unless I
undo). Why does the data column not have the same functionality (ie;
show all, filter, then go back to show all) as, say, the NAME column
(ROW field) does? Is there any way I can format the pivot table to
ensure I don't lose all the original selections/fields in the DATA area
if I don't show all? I know I can use SHOW FIELD LIST to put the
selections back in but I don't want to have to re-format each time as
this pivot table will be used by people who do not understand them or
how to reconstruct them.

Thanks for any help.

Pepikins
 
There's nothing you can do to make the data fields behave differently.
You could use programming to remove the dropdown list from the data
field button, to prevent users from deselecting one of the items. For
example:

Sub DisableDataSelection()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

pt.PivotFields("Data").EnableItemSelection = False
End Sub
 
Back
Top