Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Pivot table - refresh new rows?

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

    Default

    How can I get a pivot table to dynamically refresh/accept new rows that are added to the worksheet? The 'refresh data' option only seems to update the pivot table when existing data rows have been changed.

    Thank you

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't worry, I found the answer - using named ranges and in the 'Refers To' box typing OFFSET(=OFFSET($A$1,0,0,COUNT($A:$A),1))

    I found this on the message board referencing this site:

    http://www.ozgrid.com/Excel/DynamicRanges.htm#OFFSET


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops I meant

    =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1A:$A),16)

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-27 05:30, kiwi wrote:
    How can I get a pivot table to dynamically refresh/accept new rows that are added to the worksheet? The 'refresh data' option only seems to update the pivot table when existing data rows have been changed.

    Thank you
    Create a dynamic name range, say, Data, and give this as Range to your already created pivot table (PT):

    Activate a cell within your PT.
    Activate PivotTable Wizard that gives you a window entitled "PivotTable and PivotChart Wizard - Step 3 of 3".
    Activate the Back button, which gives you "Step 2 of 3", showing Range with its current value.
    Replace that current value with dynamic name range.

    Next time to refresh while new rows added to your data, you'll see them included.

    If you don't know how to create a dynamic name range, post the current range address of your data along with the kind of data (numbers, dates, or text) the range houses and the name of the sheet where your data is located.

    Aladin


  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-27 06:28, kiwi wrote:
    Oops I meant

    =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1A:$A),16)
    You have a typo in that formula:

    =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),16)

    By the way, if there is ever a blank cell in A, this formula will cause havoc. Change it as follows to cover the problem with possible blanks:

    =OFFSET(Sheet1!$A$1,0,0,MATCH(9.99999999999999E+307,Sheet1!$A:$A),16)

    Aladin

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
  •