I have created a pivot table using a dynamic named range as my source.
The source data consists of 52,416 rows and 10 columns showing:

Membership code
Date
Hour
Day
Week
Month
Term
Year
Checkouts
Checkins

Everything appears as expected when I go through Excel's PivotTable
wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I place
in the data area of the PT) always come up as zeros. The PT seems to
have replaced all the correct data in each cell with a zero!

In addition, I have to manually change the fields to "-Sum of-..." as
opposed to the "-Count of-..." that appears first. It feels as though
this screwy behaviour must have a bearing on why all my data is being
replaced with zeros.

I should add that this has never happened to my other PTs. In fact, I
have a PT in the same workbook with exactly the same columns and layout
with a similar amount of data that works perfectly. I just can't figure
it out!


--
MyrtlePeacock
------------------------------------------------------------------------
MyrtlePeacock's Profile: http://www.excelforum.com/member.php...o&userid=29812
View this thread: http://www.excelforum.com/showthread...hreadid=495192

The checkouts and checkins numbers in the source data may be text,
instead of numbers. To convert them to real numbers, you can use one of
the techniques shown he

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


MyrtlePeacock wrote:
I have created a pivot table using a dynamic named range as my source.
The source data consists of 52,416 rows and 10 columns showing:

Membership code
Date
Hour
Day
Week
Month
Term
Year
Checkouts
Checkins

Everything appears as expected when I go through Excel's PivotTable
wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I place
in the data area of the PT) always come up as zeros. The PT seems to
have replaced all the correct data in each cell with a zero!

In addition, I have to manually change the fields to "-Sum of-..." as
opposed to the "-Count of-..." that appears first. It feels as though
this screwy behaviour must have a bearing on why all my data is being
replaced with zeros.

I should add that this has never happened to my other PTs. In fact, I
have a PT in the same workbook with exactly the same columns and layout
with a similar amount of data that works perfectly. I just can't figure
it out!




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for the advice. I used the "Paste Special" method to convert the
text numbers into real numbers on my data list and then updated my PTs.
All the zeros were gone and I now have the correct data where it should
be.

Thanks again.


--
MyrtlePeacock
------------------------------------------------------------------------
MyrtlePeacock's Profile: http://www.excelforum.com/member.php...o&userid=29812
View this thread: http://www.excelforum.com/showthread...hreadid=495192

You're welcome. Thanks for letting me know how you solved the problem.

MyrtlePeacock wrote:
Thanks for the advice. I used the "Paste Special" method to convert the
text numbers into real numbers on my data list and then updated my PTs.
All the zeros were gone and I now have the correct data where it should
be.

Thanks again.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html