I recently discovered the tickbox "Show all data" in the Field Settings tab
of a Pivot Table. This is just what I needed as I reference data in the
table using GETPIVOTDATA and this would return N/A if the field is not
there.

What puzzles me is how does Excel know what all the field values could be if
they are not used in the underlying list. For example I have a Cash book
that divided the year into 6 periods. During period 1 the other period are
not represented but the pivot table displayed all 6 (Actually also included
a 0 field, which is extra). How does Excel know what all the field values
could be?

To test the feature I used a list with all 6 periods represented and deleted
rows with periods other than 1. Could it be that the pivot table
"remembered" all the possible field values.

Thanks
Laurence

Hi Laurence

It seems to me that it is a new feature in XL2003, that the PT includes
all options. I don't remember it doing this in earlier versions, but I
agree, that once you select Group by Months for example, all months
appear in the drop down list, even if the underlying data only includes
a single month's worth of data.

--
Regards

Roger Govier


"Laurence Lombard" wrote in message
...
I recently discovered the tickbox "Show all data" in the Field Settings
tab
of a Pivot Table. This is just what I needed as I reference data in
the
table using GETPIVOTDATA and this would return N/A if the field is not
there.

What puzzles me is how does Excel know what all the field values could
be if
they are not used in the underlying list. For example I have a Cash
book
that divided the year into 6 periods. During period 1 the other period
are
not represented but the pivot table displayed all 6 (Actually also
included
a 0 field, which is extra). How does Excel know what all the field
values
could be?

To test the feature I used a list with all 6 periods represented and
deleted
rows with periods other than 1. Could it be that the pivot table
"remembered" all the possible field values.

Thanks
Laurence

Hi Laurence,

Yes, the pivot table will "remember" all the possible field values.

The following web page of Debra's helped me & it may help you update
your pivot table fields by showing how to remove the redundant items:

http://www.contextures.com/xlPivot04.html

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=506657