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