• 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.

Change Data Source Multiple Pivot Tables

Hi,

I am looking for an easy way to change the data source on multiple Pivot Tables in one shot. By easy I am hoping without VBA but if it must be, the most efficient way. I do fiscal oversight and I have tabs per site (up to about 15) per workbook and it would be nice if I can change them efficiently. I tried looking through ASAP Utilities and KuTools but I could not really find much.

Please help! Thanks!
 
Without knowing how your pivot data sources are set up. Hard to day.

By change, do you mean that you need to change the source range completely (i.e. use different sheet/table)?
Or do you mean that you need to extend the data range?

If latter, I'd recommend setting up range as Excel table and using table name as data source. It will extend pivot data source as table grows.

If former, it will depend on your Excel version and how pivots are constructed. It would help, if you can upload sample workbook with few example pivots (make sure to desensitize the data).
 
Hi. This is the basic idea of the data that I work with. I made it a lot smaller so it could be uploaded. Usually my data source is in another workbook. I would be changing workbooks as my data source.

Thanks for your help!
 

Attachments

  • Chandoo 7.16.19.xlsm
    486.1 KB · Views: 9
I'm confused...
All pivot tables share same source. So there really is no need to update multiple pivots. Single update to connection should suffice.

How are you connecting to the external workbook that holds Raw data? MS Query, PowerQuery, etc.

And it really helps us in giving you best option, if you let us know your Excel/Office version.
 
I have a main file that I use for everyday use. Quick filtering, projections, etc. This is the one I update that other workbooks (like this one) reference to.

This workbook is for when I meet with specific people on a monthly basis. Basically an overview of certain departments that they are responsible for. The hard part is when our fiscal year ends, I still need to reference the prior year but this file is only pulling from current year data (which is my main workbook). I do it this way because instead of pasting my raw in multiple sheets, I can update my main file and refresh the rest.

Would it make more sense to just put the raw data in each workbook at that point? I would rather not because I have about 5 workbooks like this and if I can overlay just one to update all, that would be amazing. Data changes daily too. Trying to lessen the amount of manual tasks if I can.
 
Then I'd use power query or MS query to make connection to a folder, append multiple workbooks.

Then create pivot tables off of connection.

Don't have access to PC tonight, will see if I have time tomorrow.
 
Back
Top