text and special functions in Pivot table report

[Guest]

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.

 

[Epinn]

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.

 

[Debra Dalgleish]

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.

 

[Guest]

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.

 

[Debra Dalgleish]

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

 

[Debra Dalgleish]

It's hard to tell without some sample data, but from what you've
described, a pivot table may not be the best way to analyze the data.

You could add the income and expense fields to the data area, without
the subtotals and totals, and it may give you what you're after.