Ongoing Pivot Table Issue

[roly]

Hi,

Please see the attached spreadsheet.

I have created a pivot table using approximately 1,300 rows of data,
which I have reduced to 500 due to the 100Kb limit. When I tried to
move the Kilos field on the Main Report sheet into the data section sop
that I can include sub-totals for each product, I receive the error
message "Excel cannot make this change because there are too many row
or column items".

I have had this problem a couple of other times recently with hardly
any data and although I have managed to bodge something together, I
would like to be able to understand what I can do about this to prevent
it happening in the future. I have tried reducing the data to 500 rows
and studied this Microsoft link ''
(http://support.microsoft.com/default.aspx?id=820742) but it does not
really shed any light. I have also analysed a 1.4 million row query
used by a pivot table that I created recently, and this only gets this
error message when there are too many rows to fit onto the sheet.

What I cannot understand is how such a tiny amount of data will not
work correctly in a pivot table!!

Thanks ever so much for any help!!


Roly


+-------------------------------------------------------------------+
|Filename: Duty Free Meat Forum2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4053 |
+-------------------------------------------------------------------+

 

[roly]

Hi,

I am using Excel 2003 - I have only had trouble with pivots using small
amounts of data with Excel 2003. When I used Excel 2000 I never had any
problems with small amounts of data. I expect problems with loads of
data and work around it when it happens - for example, I had to use two
pivots using that 1.4 million row Access query to show all of the data.

Most of the reports I prepare detail sales in quantities for different
areas and products and we run a very large contract using approximately
6,000,000 lines on invoices each year. We need management information
and it involves large amounts of data.

I would like to be able to find a way to stop it happening - it
happened again a couple of days ago and I had to use formulas to bring
the rest of the data in for about 3000 lines of data.

Having read that Microsoft link, I tried formatting certain numbers as
text but that did not work and a few other odd things. What I really
cannot understand is why it happens, because Excel is brilliant - I
love it because you can do most things with it.

All the best,


Roly

 

[Guest]

Dear Roly,

I can not download your file.

There is no limit. You can have 1300 records (rows) or 13,000. It is only
limited to the nos of rows in Excel which is 65536.

You can use the add-in. E-mail me at (e-mail address removed) & i can sedn
you the add-in. It will be a big help.

Regards

Jawad