Automatically updating Pivot Tables across worksheets

[spencerbc4]

Basically I am trying to get this so when the "Client Name" on one Pivot Table is changed, it changes the Pivot Table on the other sheet to the same client and vice versa. The real worksheet has several more sheets, so I want to make sure it would update all sheets with a table

[ORoos]

Hi there,

You can do this via a 'Slicer'.

First, you will need a table with unique values; Client Names (you can add client ID et to the table if you want to make sure names are not duplicated).
Create your Pivot tables and tick 'Add this data to the Data Model' for each Pivot Table.
No need to set the Client Name as a filter field in the pivot table.
On the pivot table with the Client Name pivot table insert 'Slicer' and select the Client Name field (you can customize the slicer later).
With the Slicer selected, click the 'Slicer Tools' and then 'Report Connections'.
Click the other pivot tables you want to control with the slicer.

You may need to Relationship links the tables manually if excel does not find the connections automatically.

Test the Slicer.
Once happy with it (and customized the look and feel if desired), copy the slicer onto each page with the Pivot table from where you want to control the filter.

PS: You can also select multiple client names with the slicer.

I added some screenshots in the sample workbook.

Hope this helps.

[seercoven]

Hi,

I did something similar to ORoos as well without the slicers. I just include both pivot data into data model and regenerate one combine information. The filter used in sheets allow for variable option of client selecting. You can pick jen on first pivot data while picking john on 2nd pivot data. That depends on what kind of other sheets you have

If you still need the pivot from other worksheet and not a combine one, then ORoos method is the best with slicers included.

As highlighted by ORoos, a unique value like client name would make things more convenient. However if you have lots of sheets, linking manually and creating slicer would be a chores.

[spencerbc4]

Thanks. Solution verified. This is extremely helpful. I will be working with larger data sets, is there any way to incorporate this with turning off screen updates so it runs faster in the real environment? I know there's a way to do it in VBA (still learning that) but do you know off the top of your head if I can combine that with the slicer in any way to optimize?

[ORoos]

Hi again,
Screen updating, or stopping it, is more about preventing the flickering when a VBA macro runs and switches between sheets or files a lot. Not that much of a slowing down issue.
If you want to have macros in your workbook, you need to save them as macro enabled workbooks. You then need a trigger when to run the macro (turn off / on), which could be a button, keyboard shortcut or automatically whenever (specific) changes are made. Depending on how often that happens, it may does slow the process more down than saving speed.

If your file updates really slow, you could try to set the 'calculation' to manual. That way excel is not constantly re-calculating values when you make some minor changes. You then of course would need to remember to click the 'Calculate Now' button when you need all data re-calculated.
Top Ribbon > Formulas > Calculations > Calculation Options

Also consider changing your data in the sheets to 'tables'. When used for pivot tables etc. and you are adding data, the data will automatically expand to include new rows and columns.

Cheers & Thanks for the Rep