+ Reply to Thread
Results 1 to 5 of 5

Automatically select new data when refreshing Pivot Table

  1. #1
    Forum Contributor
    Join Date
    01-06-2015
    Location
    Huntsville, AL
    MS-Off Ver
    Office 365
    Posts
    173

    Automatically select new data when refreshing Pivot Table

    This is probably simple and may require some VBA, but I have (2) Pivot Tables that are pulling from 3 simple columns. Every afternoon I want to run a report. The report consist of a date and percentage. The problem is when I refresh my Pivot Tables it doesn't select the new date automatically. If I set it to Select all, then it pulls the blank data as well and doesn't chart the way I would like it to. Can someone take a look and see if you can help. I have attached a dummy sheet as an example. Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Automatically select new data when refreshing Pivot Table

    Use dynamic named range as your pivottable source.

    With your set up named range formula.
    =Sheet1!$B$1:INDEX(Sheet1!$1:$1048576,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$1:$1))

    Name it something like "pvtRange" and use F3 in Change PivotTable Data Source->Table/Range: to pick it from list of names.

  3. #3
    Forum Contributor
    Join Date
    01-06-2015
    Location
    Huntsville, AL
    MS-Off Ver
    Office 365
    Posts
    173

    Re: Automatically select new data when refreshing Pivot Table

    I am afraid you've lost me on this.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Automatically select new data when refreshing Pivot Table

    See attached. There was mistake in previous formula (since you have Column A blank, you need to add 1 to column in index formula).
    =Sheet1!$B$1:INDEX(Sheet1!$1:$1048576,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$1:$1)+1)

    You can find detailed tutorial on dynamic named range in link below.
    https://exceljet.net/formula/dynamic...nge-with-index

    When you refresh PivotTable it will automatically recalculate and grab latest data.

    You can check the formula in Name Manger (in formula ribbon).

    Alternately you can convert data source range to table.
    Attached Files Attached Files

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,581

    Re: Automatically select new data when refreshing Pivot Table

    The easiest way is, convert your data into an Excel Table and then insert pivot tables so that after you add more data down the rows, you only need to refresh the pivot tables.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Table Data Not Refreshing from Named Table
    By Seth_ in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-04-2016, 11:10 AM
  2. Replies: 16
    Last Post: 04-04-2014, 04:55 AM
  3. Refreshing the actual data source of a Pivot Table
    By mjhaston in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-01-2014, 01:59 AM
  4. Save old data when refreshing query in pivot table
    By jordanmix in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 08:10 PM
  5. Pivot Table loses formatting when refreshing data
    By Inigo Montoya in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-18-2013, 12:19 PM
  6. Refreshing Pivot table data
    By yael in forum Excel General
    Replies: 12
    Last Post: 01-09-2012, 07:51 AM
  7. Refreshing Data within a pivot table
    By KeithB in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2006, 07:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1