TOO MANY CHARACTERS!!!! - PIVOT TABLE

[PK]

A co-worker has a pivot table where one of the source columns refers to cells
that could contain more than 255 characters. The pivot table works, but
truncates the text at 255 characters.

Is there any way the pivot could show the whole text?

 

[Shane Devenshire]

Hi,

How about a little more detail, what field - row, column, data, or page.
What version? Is this a data field or a title?

Here are the posted specifications for 2007:

Feature Maximum limit
PivotTable reports (PivotTable report: An interactive, crosstabulated Excel
report that summarizes and analyzes data, such as database records, from
various sources, including ones that are external to Excel.) on a sheet
Limited by available memory
Unique items per field 1,048,576
Row (row field: A field that's assigned a row orientation in a PivotTable
report. Items associated with a row field are displayed as row labels.) or
column fields (column field: A field that's assigned a column orientation in
a PivotTable report. Items associated with a column field are displayed as
column labels.) in a PivotTable report Limited by available memory
Report filters in a PivotTable report 256 (may be limited by available
memory)
Value fields in a PivotTable report 256
Calculated item (calculated item: An item within a PivotTable field or
PivotChart field that uses a formula you create. Calculated items can perform
calculations by using the contents of other items within the same field of
the PivotTable report or PivotChart report.) formulas in a PivotTable report
Limited by available memory
Report filters in a PivotChart report (PivotChart report: A chart that
provides interactive analysis of data, like a PivotTable report. You can
change views of data, see different levels of detail, or reorganize the chart
layout by dragging fields and by showing or hiding items in fields.) 256 (may
be limited by available memory)
Value fields in a PivotChart report 256
Calculated item formulas in a PivotChart report Limited by available memory
Length of the MDX name for a PivotTable item 32,767
Length for a relational PivotTable string 32,767


Cheers,
Shane

 

[Dave Peterson]

Can you add another column to the table range that can be used as a key?

Then you could use =vlookup() or =index(match()) to retrieve the long string.

 

[pk]

Thank you both for responding...

Dave, i am not sure how i would do that - perhaps a little more detail like
Shane suggested will help....

A B
1 text
2 text
3 text

Where the text in column B could possibly be longer than 255 characters...

Then create a simple pivot table, and the text from column B will be
truncated to 255 characters in the pivot. Is there any way to show more than
the 255 characters in the pivot table?

 

[Dave Peterson]

The fields in the pivottable are limited to 255 characters.

If you can assign a nice key to each of those long descriptions, you could put
that in the first column of your range used for the pivottable.

Then you could use:
=vlookup(a3,sheet1!a:e,5,false)
to retrieve the long value in column 5 of that range based on that key.

 

[pk]

Thanks Dave! I will explore those suggestions.