Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table in Excel 2007 & 2000 (backward compatibility problem)
Hi everyone.
I created a pivot table in Excel 2007. It had 5 columns and over 1800 rows (almost 10000 cells all together) and use to work fine in both Excel 2007 & 2000. There was one feature that I didnt like however. It use to sort data in one of the columns (column no. 2). Unfortunately these data should be presented exactly as they were entered (without any sorting). To workaround I added 1 extra column. It contains numbers in ascending order. This column was placed in front of the column no. 2 and hidden. Although I got rid of sorting, a new problem occurred €“ pivot works OK in Excel 2007 only. Excel 2000 doesnt accept such pivot table and I get a message that its incorrect. If I want to reconfigure the pivot I get a message that there is to many columns or rows and some of them should be removed from the pivot. Any ideas how to solve the problem of sorting without adding a new column? Or maybe there is a way to force Excel 2000 to work with pivot in conjunction with larger amount of data? Thanks in advance for any help. gordom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table in Excel 2007 & 2000 (backward compatibility problem)
Hi,
The problem is that 2000 was has a much weaker pivot table, it can handle far fewer unique items and by adding a column of unique numbers in front you have increase the complexity beyond Excel 2000's capacity. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "gordom" wrote: Hi everyone. I created a pivot table in Excel 2007. It had 5 columns and over 1800 rows (almost 10000 cells all together) and use to work fine in both Excel 2007 & 2000. There was one feature that I didnt like however. It use to sort data in one of the columns (column no. 2). Unfortunately these data should be presented exactly as they were entered (without any sorting). To workaround I added 1 extra column. It contains numbers in ascending order. This column was placed in front of the column no. 2 and hidden. Although I got rid of sorting, a new problem occurred €“ pivot works OK in Excel 2007 only. Excel 2000 doesnt accept such pivot table and I get a message that its incorrect. If I want to reconfigure the pivot I get a message that there is to many columns or rows and some of them should be removed from the pivot. Any ideas how to solve the problem of sorting without adding a new column? Or maybe there is a way to force Excel 2000 to work with pivot in conjunction with larger amount of data? Thanks in advance for any help. gordom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table in Excel 2007 & 2000 (backward compatibility problem)
You could try to concatenate sorme columns.
Example, Ff you you have one column named Client ID and another column named Client Name, and the same Client ID always refers to the same Client Name, you could go back to your data source and create a new field called Client ID and Name which would include the ID plus a space plus a hyphen plus a space plus the Client Name. Then modify your PT layout to display Client ID and Name. So instead of having to display two columns in the Pivot Table you now have only one. (or one less.) This is a workaround to avoid memory limits in loder versions of Excel. +++++++++++ Alternatively, +++++++++++++++ say you need to display 5 columns. Start by just showing just the first 2. Then go back to the PT layout and add 1 more column. See if it works. Then go back to the PT layout and add 1 more column. See if it works. Then go back to the PT layout and add 1 more column. See if it works. Sometimes, adding in PT columns gradually 1 by 1 - instead of all together - will allow Excel to avoid the "too many columns problem". Apparently, Excel 2007 does not have this problems. Rosalie "gordom" a écrit dans le message de news: ... Hi everyone. I created a pivot table in Excel 2007. It had 5 columns and over 1800 rows (almost 10000 cells all together) and use to work fine in both Excel 2007 & 2000. There was one feature that I didn't like however. It use to sort data in one of the columns (column no. 2). Unfortunately these data should be presented exactly as they were entered (without any sorting). To workaround I added 1 extra column. It contains numbers in ascending order. This column was placed in front of the column no. 2 and hidden. Although I got rid of sorting, a new problem occurred - pivot works OK in Excel 2007 only. Excel 2000 doesn't accept such pivot table and I get a message that it's incorrect. If I want to reconfigure the pivot I get a message that there is to many columns or rows and some of them should be removed from the pivot. Any ideas how to solve the problem of sorting without adding a new column? Or maybe there is a way to force Excel 2000 to work with pivot in conjunction with larger amount of data? Thanks in advance for any help. gordom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table in Excel 2007 & 2000 (backward compatibility problem)
You could try to concatenate sorme columns. Then modify your PT layout to display Client ID and Name. So instead of having to display two columns in the Pivot Table you now have only one. (or one less.) Thanks for your help. I concatenated these 2 columns (the first one consisting supplementary numbers & second with actual data) and Excel 2000 accepted pivot of that size. What I dont know however is how to split these data inside the pivot table report? I have to get rid of this prefix (it cant be visible). Is there any way to do so? I tried few things but with no success. +++++++++++ Alternatively, +++++++++++++++ Sometimes, adding in PT columns gradually 1 by 1 - instead of all together - will allow Excel to avoid the "too many columns problem". That didn't work. Regards, gordom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Color Backward-Compatibility Issue | Excel Discussion (Misc queries) | |||
Drawing compatibility excel 2000; 2003 vs 2007 | Excel Discussion (Misc queries) | |||
Pivot Table Backward Compatibility | Excel Discussion (Misc queries) | |||
Excel 2007 backward compatibility re colors | New Users to Excel | |||
Excel 2000 pivot table problem | Excel Worksheet Functions |