text and special functions in Pivot table report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Waukee Brian - 28 Nov 2006 02:40 GMT

I work a lot with pivot tables and wondered if you can import text or special
formating into the table. Whenever I try to do this, the column shows up as
"0" and I have tried formatting about every way. For example if I want to
import a date, text, or something such as DK537RR, it shows up as zero and
not what I want. Can anyone help me.

Reply to this Message

Epinn - 28 Nov 2006 04:24 GMT

Hi,

Sounds like you choose "external data source" via the PivotTable wizard? Debra is the guru on PivotTable. Hope she sees your post soon.

Following is from Excel Help:

If you returned data from an Office Data Connection file (.odc extension) directly to a PivotTable report, or your report is based on OLAP source data, you cannot use different source data in the report.

In case you are interested, here is some good info on importing data to Excel (not to PivotTable directly though).

http://www.nickhodge.co.uk/gui/datamenu/importexternal.htm

Is it an option to import data to a worksheet and then create a PivotTable? Just curious.

Epinn

I work a lot with pivot tables and wondered if you can import text or special
formating into the table. Whenever I try to do this, the column shows up as
"0" and I have tried formatting about every way. For example if I want to
import a date, text, or something such as DK537RR, it shows up as zero and
not what I want. Can anyone help me.

Reply to this Message

Debra Dalgleish - 28 Nov 2006 05:21 GMT

You can't summarize text fields as text in the pivot table's data area,
but dates could be summarized as Max of or Min of, to show a date.

Text fields could be added to the Page, Row or Column areas, then use
other fields in the data area.

> I work a lot with pivot tables and wondered if you can import text or special
> formating into the table. Whenever I try to do this, the column shows up as
> "0" and I have tried formatting about every way. For example if I want to
> import a date, text, or something such as DK537RR, it shows up as zero and
> not what I want. Can anyone help me.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Waukee Brian - 28 Nov 2006 14:42 GMT

Thank you for replying.

Are you saying that I can add text fields in the pivot table. If so how do
I do that. Basically I have three different fields I need to add. It is for
a farm record so I need to have an field to put in a date of which crop is
planted, the seed variety used(ex. DK537RR) and also a field to put in a
fertilizer analysis using the following formula:

=IF(E105>0,'Farm sheet'!CY149&"-"&'Farm sheet'!CY150&"-"&'Farm
sheet'!CY151,"")

If you could help me get these into my pivot table somehow, I would be
eternally greatfull.

> You can't summarize text fields as text in the pivot table's data area,
> but dates could be summarized as Max of or Min of, to show a date.
[quoted text clipped - 7 lines]
> > import a date, text, or something such as DK537RR, it shows up as zero and
> > not what I want. Can anyone help me.

Reply to this Message

Debra Dalgleish - 29 Nov 2006 04:27 GMT

What are you trying to summarize in the pivot table? If you have a date,
and two text fields, do you want a count of which seeds were planted per
month, and with which fertilizer?

You could put all three fields in the Row area, then add another copy of
the seed field to the data area, where it will become Count of Seed
Variety. To group the dates, you can follow the instructions here:

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

> Thank you for replying.
>
[quoted text clipped - 21 lines]
>>>import a date, text, or something such as DK537RR, it shows up as zero and
>>>not what I want. Can anyone help me.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message