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.

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/datam...rtexternal.htm

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

Epinn

"Waukee Brian" <Waukee wrote in message ...
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.

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.

Waukee Brian wrote:
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
Contextures
http://www.contextures.com/tiptech.html

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(E1050,'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" wrote:

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.

Waukee Brian wrote:
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
Contextures
http://www.contextures.com/tiptech.html

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 he

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

Waukee Brian wrote:
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(E1050,'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" wrote:


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.

Waukee Brian wrote:

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
Contextures
http://www.contextures.com/tiptech.html





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

Basically what I am doing is making a P&L for a farm. On the pivot table I
have a list of income and expenses that are summarized on the pivot table.
What I am trying to do is insert the date planted, Fertilizer
analysis(equation), Seed variety, and seed population(forgot that one
yesterday) into the pivot table as reference information and make the pivot
table look more usefull and see if any of the same reference data would show
a correlation between better yields or more/less profitability of the farm.
Maybe I can't do that I don't know. So I have the data along the left column
and each column after that is field 1, field 2, etc. and a total farm
summary. The data I want to enter into the pivot table would be under every
field that is included in the farm. If I could attach one of the pivot table
pages to show you what I am trying to do, that might help, but it doesn't
appear that I can do that. Maybe this will help.

Data Field: Farm Summary - Field 1 - Field 2 - Field 3
- Field 4 - etc
Corn Acres:
Yield bu/ac
Grain Sales
Crop Ins Guaranteed Income:
Crop damage claims
Gov't Payments:
Total Income: (total from above)
Expenses
Mortgage Payment:
taxes:
Rent expense:
Fertilizer expense:
Cust app:
Chemicals:
Seed:
Crop Ins. Premium
Drying
Storage
loan annual payment
Repairs:
Fuel:
Utilities:
Tillage:
Planting:
harvesting:
Operating Interest:
Insurance:
Labor
Marketing
Total Expense:
Net Profit/Loss:

All of these numbers are summarized. What I want to add under corn acres
are the 4 reference data fields shown above that are nothing more then
reference data to see how that data affects the net profit/loss. I hope this
makes it more clear.

Thank you for taking the time to look at this. I appreciate your help.


"Debra Dalgleish" wrote:

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 he

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

Waukee Brian wrote:
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(E1050,'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" wrote:


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.

Waukee Brian wrote:

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
Contextures
http://www.contextures.com/tiptech.html





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

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.


Waukee Brian wrote:
Basically what I am doing is making a P&L for a farm. On the pivot table I
have a list of income and expenses that are summarized on the pivot table.
What I am trying to do is insert the date planted, Fertilizer
analysis(equation), Seed variety, and seed population(forgot that one
yesterday) into the pivot table as reference information and make the pivot
table look more usefull and see if any of the same reference data would show
a correlation between better yields or more/less profitability of the farm.
Maybe I can't do that I don't know. So I have the data along the left column
and each column after that is field 1, field 2, etc. and a total farm
summary. The data I want to enter into the pivot table would be under every
field that is included in the farm. If I could attach one of the pivot table
pages to show you what I am trying to do, that might help, but it doesn't
appear that I can do that. Maybe this will help.

Data Field: Farm Summary - Field 1 - Field 2 - Field 3
- Field 4 - etc
Corn Acres:
Yield bu/ac
Grain Sales
Crop Ins Guaranteed Income:
Crop damage claims
Gov't Payments:
Total Income: (total from above)
Expenses
Mortgage Payment:
taxes:
Rent expense:
Fertilizer expense:
Cust app:
Chemicals:
Seed:
Crop Ins. Premium
Drying
Storage
loan annual payment
Repairs:
Fuel:
Utilities:
Tillage:
Planting:
harvesting:
Operating Interest:
Insurance:
Labor
Marketing
Total Expense:
Net Profit/Loss:

All of these numbers are summarized. What I want to add under corn acres
are the 4 reference data fields shown above that are nothing more then
reference data to see how that data affects the net profit/loss. I hope this
makes it more clear.

Thank you for taking the time to look at this. I appreciate your help.


"Debra Dalgleish" wrote:


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 he

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

Waukee Brian wrote:

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(E1050,'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" wrote:



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.

Waukee Brian wrote:


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
Contextures
http://www.contextures.com/tiptech.html




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





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