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 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

[cs02000]

I believe the newer versions of Excel allow for more rows of data to be
displaced. Also, you have to start to question the value of any report
with thousands of lines.


--
cs02000

[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

[JAA149]

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 [email protected] & i can sedn
you the add-in. It will be a big help.

Regards

Jawad

"roly" wrote:

>
> 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
> ------------------------------------------------------------------------
> roly's Profile: http://www.excelforum.com/member.php...o&userid=24631
> View this thread: http://www.excelforum.com/showthread...hreadid=487127
>
>