+ Reply to Thread
Results 1 to 5 of 5

How to auto update Data source row value in the Pivot Table when clicked on Refresh all ?

  1. #1
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    How to auto update Data source row value in the Pivot Table when clicked on Refresh all ?

    Hi all,
    Hope all is good.

    Can anyone please help me in getting a solution for the below mentioned problem ?

    I have created a Pivot table and based on that I have created a Chart as well.

    But when I add or delete some data under "Data" sheet, the "Data source" row value in the Pivot Table will still be the same.

    Can anyone please tell me on "How to auto update that 'Data source' row value in the Pivot Table when some data is added or removed instead of changing the data source row value manually everytime.

    Thank you all in advance for your time and for the solution that has been provided.
    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,855

    Re: How to auto update Data source row value in the Pivot Table when clicked on Refresh al

    Either convert your data range to Excel Table, or used dynamic named range.

    Then use either table name or named range as your source for pivot table.

    Dynamic named range formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    0.JPG
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: How to auto update Data source row value in the Pivot Table when clicked on Refresh al

    You could make an Excel Table of your data table (Table1)
    Then change the source of your Pivot table to Table1
    Add the VBA code to the worksheet where your pivot table is:
    Please Login or Register  to view this content.
    Then save your workbook as *.xlsb (that can save your VBA code)
    When you make modifications to your data table and you jump to the pivot worksheet it will automatically be refreshed.
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  4. #4
    Registered User
    Join Date
    06-26-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    81

    Re: How to auto update Data source row value in the Pivot Table when clicked on Refresh al

    Hi CK & PietBom,

    Appreciate your quick response and with the solution that I was searching for.
    Its working Perfectly as per my requirement.

    Thanks a lot for your solutions!!!!

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

    Re: How to auto update Data source row value in the Pivot Table when clicked on Refresh al

    You are welcome. Thanks for the rep.

+ 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. Pivot Table Auto Update Data Source?
    By Ket in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2020, 02:05 PM
  2. Auto-Refresh Pivot Table When Source Data Refreshes
    By XLINT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2015, 11:00 AM
  3. Macro to auto update data range and refresh pivot table does not work
    By aikido1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2014, 05:59 AM
  4. Can't Refresh Pivot Table When Using External Data Source
    By yenping.fu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-02-2014, 06:24 AM
  5. Replies: 0
    Last Post: 08-14-2013, 06:31 AM
  6. Macro to refresh pivot table and update data source
    By TBO in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2013, 01:09 PM
  7. [SOLVED] Auto Refresh Pivot Table Once External Data source Changes
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2012, 10:33 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