Changing Pivot Table Source Data w/ Filter Controls

[seethesun]

Hi,

I have a report with multiple pivot tables (PT) that feed from the same source data. The source data is from a web query, so I can't give it a table name as it seems to discard it when it's refreshed.

There are rare occasions when new columns are added to the raw data so I need to change the source data for those PTs. However, I have a slicer tied to all of these same PTs as well. It becomes a hassle to update each PT's source data to include the new columns because it makes me disconnect them from the slicer before I can change it.

It wouldn't be too much of a hassle except the slicer is filtering to show only 13 months and these PTs are all on the same tab. So it won't just let me remove them from the slicer because it gives me the error that you cannot overlap PTs. Now, I have to go to each table and filter so it shows fewer months to avoid that error. Then, change the data source for the PTs, reapply the slicer, and remove the manual filter.

Any shortcut to this? As I said, it doesn't happen all that often, so doing it this way isn't the end of the world. I simply want to know if there is a better way.

[dflak]

Since you can't assign the source data to a table, then do it using the pre-2007 method: define a named dynamic range using the offset command.

Something like =OFFSET(Data!$A$1,0,0,Counta(Data!A:A),Counta(1:1) should work.

[seethesun]

Great. Thanks!