Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Pivot Table - automatically determining range of Source Data

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello again,

    I just had a follow up to my previous post:

    What I am trying to do is simply "refresh" an existing Pivot Table to reflect the source data range (ie, A2:G3002) at that moment. The source data range will fluctuate from week-to-week (ie, 3099 rows the next week). So I just want the pivot table to remain current, without having to manually go back into the "Pivot Table Wizard" and specify a new range each time (A2:G3099 this time). Does anyone have macro/VBA code which can dynamically update an existing pivot table such as this? Any assistance would be greatly appreciated.

    Thanks,

    Rob (again, I know!)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You don't update the PivotTable's data range. You use a named range (e.g., Database) that is updated each time the data list is extended.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assign a cell say a1 to hold the value of number of rows in your original data in the pivot table using the counta(g:g). then use concatenate to create the string ("a1: g" , a1) in a cell say a2
    Then in the pivot table wizard use indirect(a2) and refresh, it works like a charm.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...Then in the pivot table wizard use indirect(a2) and refresh, it works like a charm.
    Don't quite understand why you think this works -- it doesn't. The PivotTable wizard expects a range or named range and Step 2 of 4. Nothing else will work!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cell a2 has a range address!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 15:06, karraj wrote:
    cell a2 has a range address!
    A2 has a text representation of a cell range. That's quite different! Furthermore, Step 2 of 4 in the PivotTable wizard will only accept a range or named range. =INDIRECT(A2) is neither of these. It's a formula.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I looked up my model. I have named A2 as sourcerange and using indirect(sourcerange) in step2 of pivot table wizard. It works.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 15:25, karraj wrote:
    I looked up my model. I have named A2 as sourcerange and using indirect(sourcerange) in step2 of pivot table wizard. It works.
    What version of Excel are you using? XL97 will not accept INDIRECT(A2) at Step 2 of 4. XL2000 "chokes" at Step 3 of 3 when you attempt to define the Layout.

    Both XL97 and XL2000 will allow you to create a named range that refers to =INDIRECT(A2) and then use that named range at Step 2, but this is approach has no advantages over my original suggestions. Nothing is gained by storing a text representation in a worksheet cell and then referencing it from a named range.

    [ This Message was edited by: Mark W. on 2002-03-11 15:42 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I first created the pivot table and then did this approach to make my table dynamic.

    how do you dynamically change the named range?

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using XL97 and did not encounter any problems.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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