Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Does not calculate (2003 SP1)

    I have 2 worksheets with GETPIVOTDATA formulas referencing a pivot table in the same workbook. Each has a command button that programmatically modifies the pivot table into the needed configuration, but the formulas don't automatically calculate (even though calculation is set to automatic). I added Application.CalculateFullRebuild to the end of the command button code, but that doesn't help. The only 'solution' appears to be to tell the users to press Alt+Ctrl+F9 after clicking the command button (and that does work), but that's obviously not ideal.
    I can't find any mention of the problem in the MS Knowledge Base. Anybody heard of this, or have any ideas on workarounds? TIA

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

    Re: Does not calculate (2003 SP1)

    Could you post a small sample workbook that demonstrates the problem? Make sure not to include sensitive data.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Does not calculate (2003 SP1)

    Attached is a sample workbook. It includes the formulas and sort of illustrates what I mean, except for one thing... in the attached workbook the formulas *do* update! Which suggests it's related to file size or (more likely) the number of formulas. In the meantime, I've tried other ideas like copying and pasting the entire range on top of itself; incorporating a user-defined function in the worksheet that's defined as volatile; but nothing seems to work. I also tried changing th GETPIVOTDATA formulas to explicitly tell it the fields involved - but no change. Thanks for your help.3

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

    Re: Does not calculate (2003 SP1)

    I can't find anything specific about GETPIVOTDATA formulas not recalculating.

    If you have the time, you could experiment with the size of the data and/or the number of formulas to see if recalculation quits at a certain point.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Does not calculate (2003 SP1)

    Thanks for looking. FYI, it seems to be related to the "old" way of specifying the parameters in the GETPIVOTDATA function. If the parameters are GETPIVOTDATA(pivot_table, "item1 item2"), it behaves as I described. If I use what I think is the new way, i.e., GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2), then it behaves as it should straight away. Unfortunately, I don't think I can use the new format...

Posting Permissions

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