Pivot table field selection based on selection in other Pivot

**whahnelt** · 10-16-2019, 04:57 AM

Hi,

After so much great help from this forum, I try my luck with below question - not sure it's actually doable but if so, someone here will know :-)

I have an analysis that is based on 3 hidden pivots. 2 of them need to be set to the year I want to analyse (which I solve with a slicer linked to both pivots). Now the third one needs to be set to "previous year". For now, I solve this with a second slicer but I was wondering if there is a way to make that one "linked" somehow as sometimes people forget or select the wrong year. So, can I make a pivot table select a value filter for year based on what is selected for another pivot??

I guess a macro would help - not very familiar with those though... So any non-macro suggestion would be great. If only doable with macro, anyone can help me with the code?? Working with Windows 10, 64-bit and MS Office Professional Plus 2013 if this is needed...

Thanks!!
Wiebke

**PaulM100** · 10-16-2019, 05:27 AM

You could try something else. In a hidden cell add(for my example macro, add it in A1) like =Sheet2!B2-1(Sheet2!B2 being the year cell for those 2 pivots - 2018 in your case)
And then, right click on worksheet, view code and drop this:

Please Login or Register  to view this content.

Make changes as necessary for the portions marked in red

**whahnelt** · 10-16-2019, 09:05 AM

Thanks a lot for the fast help! I will try this out once this month's reporting is done. If I struggle, I may come back with a question but it seems pretty easy to follow - so a big thanks for that already!

**whahnelt** · 10-17-2019, 02:35 PM

Reply deleted, not valid anymore. Please see other reply.

**whahnelt** · 10-21-2019, 07:52 AM

Hi again,

I now tried and actually got the code to change the selected year in my third pivot. Unfortunately, now all years are selected as soon as I make a change to the year in the first pivot.
Maybe because the year in the incoming report is stored as text but a number in D2?? If this could be the reason, can that be somehow solved in the code, too?? Changing the quite large incoming report every week would not be a very efficient solution, I'm afraid...

Also, I had to add a fourth pivot, which also should be updated to the year in D2.

My worksheet is called "PIVOT", the pivots that should be updated are "PivotDetails_PY" and "PivotSummary_PY". And the year to select for those two pivots is in cell D2.

Can you help me update the code to make it work??

Originally Posted by PaulM100

You could try something else. In a hidden cell add(for my example macro, add it in A1) like =Sheet2!B2-1(Sheet2!B2 being the year cell for those 2 pivots - 2018 in your case)
And then, right click on worksheet, view code and drop this:

Please Login or Register  to view this content.

Make changes as necessary for the portions marked in red

**PaulM100** · 10-21-2019, 09:18 AM

Upload a sample. To upload: Go Advanced - Manage Attachments -

**whahnelt** · 10-21-2019, 01:02 PM

Originally Posted by PaulM100
Upload a sample. To upload: Go Advanced - Manage Attachments -

OK, now I got home (our firewall doesn't let me upload at work…) so here's a sample file - very much simplified but same names for worksheet and pivots and same cell for previous year as my original.
Thanks a lot already for your help!!

**whahnelt** · 10-22-2019, 03:32 AM

Originally Posted by PaulM100

Upload a sample. To upload: Go Advanced - Manage Attachments -

I SOLVED IT!! Based on your code and with a little support from google and other forum entries I managed to understand enough to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then copy-pasted that section for the second pivot - and now it's working, wohoo! :-)

Thanks a lot for a great push in the right direction!!

**PaulM100** · 10-22-2019, 04:07 AM

That's the best way to learn. If you need help again, please ask.

Pivot table field selection based on selection in other Pivot

LinkBack

Thread Tools

Rate This Thread

Display

Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Re: Pivot table field selection based on selection in other Pivot

Thread Information

Users Browsing this Thread

Similar Threads

Drop Down List Selection in Pivot Table - save selection to data source?

Change Pivot Table Column field on Slicer selection (Excel 2013)

I must enable PIVOT TABLE default field selection formatting

Update Page Field on selection of another page field in excel pivot table

Disable Selection on Pivot Table field, multiple tables

Pivot Table Field Selection via VB

Selection of multiple values for pivot table field

Bookmarks

Bookmarks

Posting Permissions