Pivot Tables compatibility - 97 and 2000 ?

[Clarence Crow]

Hello
One our clever Clients has sent us a very complex Excel 2000 workbook
including Pivot Tables. He also has locked it except in a few areas to
enable us to enter Costing Data. I actually had to unlock a few of his
Sheets to be able to link 584 rows of data twice by dragging down each
column.
Unfortunately the office I'm currently in has Excel 97 and when I
looked for some of the analytical results dependent on the Pivot
Tables, all the Results Cells were empty. (I'm not up to speed on
Pivot Tables either).
The balance of the Workbook was functioning OK and produced Summary
Data where required.
However, there WAS a warning popped up when Saving that "Some Data may
be Lost"

So I've looked on groups.google.com and it suggests there may be
compatibility issues re the Pivot Tables 2000 back to 97.

I can get a copy of 2000 if this is necessary.

We have to complete this exercise and send the Costed Workbook back to
the Client rather URGENTLY.

Can anyone shed some light here as to what needs to be done?

Clarence

 

[Mick Wilcox]

Might be a red herring but you should link cells to pivot tables by typing
an equals in the cell and then clicking on the bit of the pivot you're
interested in. This inserts the 'getpivotdata' function. The syntax of
getpivotdata changed between Excel 2000 and Excel 2003 loosing the link if
opening a spreadsheet created in 2k3 in 2k - might be the same type of
thing.

 

[Debra Dalgleish]

Perhaps you could contact the client, and describe the problem that
you're having. There may be other causes for the incompatibility, such
as macros that are running.

The following MSKB article has information on the pivot table limits in
Excel 97, and links to articles on other versions:

XL97: Limits of PivotTables in Microsoft Excel 97
http://support.microsoft.com/default.aspx?id=157486

The limits for Excel 97 and Excel 2000 look identical.

There were very few changes in the pivot tables between versions, except
for the addition of PivotCharts reports. The following list of new
features is from Excel 2000 help -- most changes are formatting related,
and shouldn't affect performance:

'====================
Lay out reports directly on worksheets (New in 2000) After you click
Finish in the PivotTable and PivotChart Report Wizard, blue outlined
drop areas appear on your worksheet and the PivotTable toolbar displays
a list of the fields from your source data. You can lay out the
PivotTable report directly on the worksheet by dragging the fields from
the toolbar to the drop areas.

PivotChart reports (New in 2000) The new PivotChart report brings the
power of PivotTable reports to your charts. PivotChart reports are
interactive and have field buttons that you can use to show and hide
items in a chart.

Indented formats (New in 2000) You can specify that a PivotTable
report appear in an indented format — similar to traditional banded or
formatted database reports — which makes a large or complex PivotTable
report easier to read.

PivotTable AutoFormats (New in 2000) You can use PivotTable
AutoFormats to display indented and nonindented PivotTable reports, and
you can set PivotTable print options to set page breaks and repeat row
and column labels for PivotTable reports that appear in an indented format.

Display and hide items in fields (New in 2000) Row and column fields
now have field drop-down arrows . Click the arrows to display and select
from a list of available items. The list provides a quick way to show
and hide items in fields.

Data selection and formatting (Improved in 2000) You no longer have to
use PivotTable selection when you format a PivotTable report. Formatting
that you apply by using regular Excel selection is retained when you
refresh or change the layout.

OLAP source data (New in 2000) You can create PivotTable reports from
OLAP databases and create OLAP cubes from your queries for other
databases. OLAP databases and cubes organize the data for PivotTable
reports, making it faster to retrieve and update data than when using
traditional databases.

Interactive PivotTable list component for Web pages (New in 2000) You
can make a PivotTable report available on a Web page as a PivotTable
list, which is a component that lets users interact with the data in the
Web browser. Users can also refresh the data, change the layout, and
select different items for display.

'======================

 

[Clarence Crow]

Might be a red herring but you should link cells to pivot tables by typing
an equals in the cell and then clicking on the bit of the pivot you're
interested in. This inserts the 'getpivotdata' function. The syntax of
getpivotdata changed between Excel 2000 and Excel 2003 loosing the link if
opening a spreadsheet created in 2k3 in 2k - might be the same type of
thing.
<snip>

If you read my post correctly, nothing you've said above is relative
to possible conflict from 2000 <- 97. (we are using 97)
The 'getpivotdata' function is in there, but inserted by the Client
who was the Author of the workbook.

Our purpose in this exercise is to make Data Entries only in the
Unlocked Cells on 3 tabbed Sheets, 2 of which are Matrices which
definitely seem to have Macros running behind them to generate
Analytical Data from the Pivot Tables. This is what's NOT happening.

Unfortunately, for the Client, he has closed his Office for the
Xmas/New Year Break, (and so have we, today),so we cannot query the
integrity of his workbook until we all return to business on Jan 5,
2004.

Clarence

 

[Clarence Crow]

Perhaps you could contact the client, and describe the problem that
you're having. There may be other causes for the incompatibility, such
as macros that are running.

The following MSKB article has information on the pivot table limits in
Excel 97, and links to articles on other versions:

XL97: Limits of PivotTables in Microsoft Excel 97
http://support.microsoft.com/default.aspx?id=157486

The limits for Excel 97 and Excel 2000 look identical.

There were very few changes in the pivot tables between versions, except
for the addition of PivotCharts reports. The following list of new
features is from Excel 2000 help -- most changes are formatting related,
and shouldn't affect performance:
<snip>

Thanks for the plethora of info re Excel 2000, but it is of little use
to us, as we are using Excel 97.

Our purpose in this exercise is to make Data Entries only in the
Unlocked Cells on 3 tabbed Sheets, 2 of which are Matrices which
definitely seem to have Macros running behind them to generate
Analytical Data from the Pivot Tables. This is what's NOT happening.
The other Sheet with Unlocked Cells accepts Data and Summarises up to
a number of other tabbed Sheets in hierarchal order.
There are 12 tabbed Sheets in all, plus a hidden Database of Wages
Structures, which is linked into the 2 Matrices to generate Wages and
Trade Classifications by WBS Areas. (This looks to be where the Pivot
Tables are supposed to operate).
To satisfy OUR Accounting methods, we have to Cost the Project in OUR
own Workbook Structure, add some modified Costing Columns and then
Link these to the Client's Workbook specific Sheet. When we are
satisfied that all Costings are correct and agree from one to the
other, we Copy 2 columns in the Client's Workbook specific Sheet and
Paste Values in to sever the Links. Then we send it back to the
Client.

Unfortunately, for the Client, he has closed his Office for the
Xmas/New Year Break, (and so have we, today),so we cannot query the
integrity of his workbook or any possible solutions, until we all
return to business on Jan 5, 2004.

Clarence

 

[sebastian]

I know the problem described by Mick unfortunately.
I realized a spreadsheet with linked cells to pivot tables
with 2k2. Execl 2K give "#value" instead the result.
Is there a solution for that?
"Save as Excel 2k" doesnt help.

sebastian.federATratz-berlin.de