Dynamic Table Range for Multiple Pivot Tables connected by the same Filter Connections

Helppls

New Member
Joined
Aug 10, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Hi,

My Spreadsheet consists of multiple pivot tables/ pivot charts all from to the same datasheet with the same range. I have connected them to same slicers in order to see specific data and have them change simultaneously. I can manipulate Data within the range and have it update in the charts and tables, but I cannot add data. If I try to put e.g. A:P as range, I cannot apply filter connections. If I want to add data, I have to remove all filter connections individually, update all table ranges individually, and then reconnect all to the slicers again.
As this is quite tedious, I wanted to ask whether there is a simpler or faster way to do this. I am not experienced in Macros, I can record them but writing code is not my field of expertise.

Any help would be appreciated!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hi Helppls

Just for some clarity, how are you connecting your slicers/pivots to your tables? Are you defining the ranges (i.e. A2:P300) or are you using the table name (i.e. Table1)?
If you are using the table name, then I cannot help, but if you are setting a range.. Then try using the table names instead (this can be found by clicking anywhere in the table and selecting the "Table Design" tab in the ribbon).

You should not need any macros for this. As a rule, your tables should be dynamic; so as long as the slicer/pivot is using the table name then it should work.
 
Solution

Helppls

New Member
Joined
Aug 10, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Thank you so so much. My Data Sheet was just raw data, no table, so I had to put a definite range. I inserted the table as you said, updated the ranges and everything works perfectly and dynamically. Such a simple solution and I didnt think of it after 4 hours of looking at the spreadsheet.
 

Forum statistics

Threads
1,181,764
Messages
5,931,910
Members
436,808
Latest member
Dalton50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top