Pivot Table Too Many Row or Colum Error 1

[planopeets]

I'm trying to create a Pivot Table, which lists the Customers in the left most column and the 12 months accross in a Row. The Pivot table needs to sum the revenue for each customer by Month. There are some other misc fields I'm adding to the mix.

I get the following error message from excel:
MS Excel cannot make this change because there are too many row or column items. Drag at least one row or coumn field off the Pivot Table report, or to the page position. Alternatively, right-click a field and then click hide or hide levels on the shortcut menu.

Anyway around this error?

My work around was to the List the Customer, then then sum the months under the Customer, in the first column. This is not the best way to display this information

 

[xlbo]

I'm guessing you are using xl97 - in which case pivot tables are created by using an extra (xlveryhidden) sheet which holds all the calcs. The calcs are changed dynamically dependant on the layout of the PT and to this end, the number of calcs can vary dependant on the visible fields and layout. Unfortunately, the calc sheet is restricted to the same limitations as a standard excel worksheet ie 65536 rows and 256 coloumns. In this instance, the PT you are trying to create needs more rows or columns than the sheet has - hence the error. It's very annoying and I never found a way round it (till we upgraded office where PTs are created differently)
Basically, you just have to show less info in the ROW fields (ie have less of them) as these seem to create the most extra calculations on the hidden sheet. If the extra ROW fields are reference data then you can add these to the right or left of the pivot table by using lookups on the customer code - not a very elegant solution

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[planopeets]

I'm using Office XP.

Basically I'm looking for my pivot table to look like the following:

Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
Customer 1 $ $ $ etc
Customer 2 $
Customer 3 $
etc.

So for Rows we are talking a max of 12 - 14 depending on filter, the Customers in the column could be as high as 250.

 

[xlbo]

Interesting then
Coupla points - by ROW field, I actually mean what lists VERTICALLY - this is how it is defined within the pivot table

Doesn't seem like you would have a problem for 250 customers - can you show us the data layout of the source data and the exact layout you are trying to make in the pivot table - ie are there any extra fields apart from Customer before the data fields ??

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[planopeets]

Below is a shot of the data,starting with Jul, that is the labels, then starting with $2,268, that is the first line of data, not sure how else to show you the data. I want to correct my previous, at a summary level we are looking at about 200 customers, but the data file has about 900 recs, so each customer can have mult revenue recs to sum. The Months, Region, are before Customer in the data file.

Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Region Customer Name Start End % Alloc Status Division Rate Product Area
$2,268 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 NA ONEOK Inc. Aimee Stewart 7/1/2003 7/3/2003 63% Assigned IUS $150.00 Banner CIS

 

[xlbo]

Ok - that didn't come over too well
If you want, you can send me the file and I'll have a look Just remove or modify any sensitive data
Geoff.Barraclough@Punchpubs.co.uk

gonna be leaving work shortly but will take a look tomorrow if you send tonight

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[xlbo]

Have sent the file back - not sure what was causing the error but I didn't have any probs putting the data in the layout I think you wanted it in

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[planopeets]

Thanks.

The error I was getting was at the creation of setting up the Pivot table. By creating a Pivot table, then moving Sum of the Months to the Row, it got around my problem.