• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot chart - Auto change Data source

J

justdream

New Member
Dears,


I've workbook contains more than 10 Tabs each has different Pivot chart

Each Day when I get new RawData

I go to each Pivot table and do manually change Data source


Is there smart way so Data could be automatically updated?
 
Hi there.


I would use the offset formula for named ranges. It's a one off task and you should then be able to click Refresh All to update the pivot tables.


Let's say sheet 1 is named "Data". It has columns A-D, and 10 rows of data.

Create a named range called "Data", and add the following formula to the Refers To section:

=Offset(Data!$A$1:$D$1,0,0,Counta(Data!A:A))

This assumes that column A will have no blank values, as it needs to count each cell.


The pivot table for the "Data" tab will now need to have the data range updated to "Data". From that point on, any additional rows added to the data will be taken into account on the pivot table by simply refreshing.


This is my first response so let me know if that makes sense...
 
You must log in or register to reply here.
Back
Top