Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    PivotTable Refresh (Excel2000)

    I have a simple pivot table summing orders by category, the totals being used by a master sheet with the GetPivotData command.
    Each week the volume of data changes, so I go into the pivot table and change the range before refreshing. Is there a way to have the pivot table automatically set the range for the latest data set, or should I just do the simple fix of setting the range far larger than I will ever need? <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PivotTable Refresh (Excel2000)

    See the attachment that explains how to set up a dynamic range name.
    This allows records to be added to to the source and the range name updates automatically to supply the pivot table (or any other item) the latest range of info.
    I have only used it on charts, but it should work with pivot tables too! It works like a dream!!!
    Regards,
    Rudi

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: PivotTable Refresh (Excel2000)

    This looks interesting Rudi, will have to get down and try this out in my scenario. I need to get my head around how this owrks, your explanation is valuable. Many thanks for this.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: PivotTable Refresh (Excel2000)

    Rudi, works like a charm thank you, once I have updated all the sheets I can see significant time savings. Your explanation was more than helpful, I can see me using this a lot from now on.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PivotTable Refresh (Excel2000)

    GREAT...plain <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25> from here!!!
    Regards,
    Rudi

Posting Permissions

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