Pivot Tables - Field data

Posted by Brent Williams on September 13, 2001 6:17 PM

When the values of a field in source data are changed, the pivot tables do not update the field, but just append the new values to the field.

For example, if a field in source data is a "month" that was represented as "1,2001" before and was changed to say "Jan 2001". The pivot table referring to that, has both 1,2001 and Jan 2001. It does not erase 1, 2001 from its memory even after refreshing data.

Also, I need the "Show items with no data" check box to be checked "yes" because I have fields with no data that I need to show.

I would really appreciate it, if somebody can help me with this!!

Thanks
Brent

Posted by Mark W. on September 14, 2001 8:04 AM

Brent, Excel PivotTables "remember" all item values
of a given field. These values become the domain
of that field which is displayed in its entirety
when "Show items with no data" has been checked.
The only way that you can eliminate these items
from your PivotTable is to create a new PivotTable
from scratch. While this "memory effect" of
field items seems to be an undesirable feature, it
can be quite useful. For example, it you're
creating a PivotTable from an external data source
you can first "train" a PivotTable to recognize
domain values from a worksheet-based data list
that has the same field (column) names as your
external data source. This allows you to use the
"Show items with no data" functionality to good
effect with an external data source whose contents
you cannot control.