Pivot Table Data Filter Options Disappear

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


Pepikins wrote:
> 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
>
>


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