Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables (XP / 2000)

    I have a customer who has produced a workbook in XP that includes Pivot Tables but we work on 2000. Is there any way to convert XP pivot tables to 2000. My customer is say that when he tries it with a Save As to file type Excel 2000 it does not work and thinks he has to rewrite all his formulae.
    Neil Eustice
    Woody Worshipper

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables (XP / 2000)

    The Excel file format hasn't changed since Excel 97, so Excel 2002 (aka XP) doesn't offer an option to save as an Excel 2000 workbook. Excel 5.0/95 is the most recent format before 97/2000/2002.

    Which elements of the Excel 2002 workbook don't work correctly in your Excel 2000?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (XP / 2000)

    Hi Hans,

    Sorry for the bum stear, not having Excel XP I was not aware of this. My customer states "My main concern is when the document is saved - all of the formulas in [Figures for report] change to #VALUE. This would indicate to me that our version of excel does not like the GETPIVOTDATA formulae that I have created on my client's machine."

    The client's machine being XP and ours being 2000.

    Neil
    Neil Eustice
    Woody Worshipper

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables (XP / 2000)

    GETPIVOTDATA was introduced in Excel 97, so it should be available in Excel 2000. If the customer has tried to save the workbook in Excel 5.0/95 format, however, GETPIVOTDATA in formulas wouldn't work; in fact, the conversion should replace such formulas with their results - see MSKB article XL: Features and Formatting Lost Saving to Excel 5.0/95.

    Have you actually tried the original Excel 2002 workbook on your machine?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (XP / 2000)

    Hi Hans,

    I am afraid I have been working blind up until now as I have not actually had access to the workbook. I have asked whether I can get access to this. If I have any further joy I will keep you posted, meanwhile, thanks for the assistance.
    Neil Eustice
    Woody Worshipper

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (XP / 2000)

    OK, I have now looked at this spreadsheet in Excel XP (where it works) and Excel 2000 (where it doesn't). I have found out why, but now don't know what to do about it.

    In XP the Getpivotdata function consists of GETPIVOTDATA(DATA_FIELD, PIVOT_TABLE, FIELD1,ITEM1, FIELD2...) whereas in Excel 2000 it consists of GETPIVOTDATA(PIVOTTABLE,NAME).

    There are over two hundred getpivotdata functions in this spreadsheet and I fear they have to be rewritten to pass from one to the other. As this is going back and forwards between different versions, do you know of any magic formula to get this working back on 2000 without having to rewrite the lot? The original spreadsheet was written in XP using all of the afore mentioned options).
    Neil Eustice
    Woody Worshipper

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables (XP / 2000)

    You could write a macro that searched the sheets for the Getpivotdata formula and then read the parameters and changes them all to one version or the other (I assume you would want to use the XL2000 and lose the "extra features" in the XP)

    Presumably the XL2000 version will work in XP, if not, you will have to use a different function that works in both.

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables (XP / 2000)

    I don't think that there is an easy solution, since the syntax is really different. If you want to use the workbook in both 2000 and 2002, the best way is probably to create a custom function that returns the desired results. I'm afraid I don't know how to do that, since I can't test on Excel 2000.

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (XP / 2000)

    Thanks guys, I guess we have learnt a valuable lesson here about the Getpivotdata function. If we have people swapping data between versions - we have to be aware of any changes to that function that Microsoft have made - no matter how well they have not promoted the differences!

    Hey ho - end result is more work. Thanks anyway.

    Neil
    Neil Eustice
    Woody Worshipper

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •