Changing filter on 2 pivot tables based on single cell value

kidwispa

Active Member
Joined
Mar 7, 2011
Messages
330
Hi All,

I've been tasked with the job of creating a report that shows all information for a particular period based on the period selected from a data validation list (eg, number 1 selected returns all data for period 1, 2 for period 2, etc). The cell reference for this list is cell D2 on a worksheet called "Report".

The majority of the work is complete and working however the one area I'm struggling with now is a way of showing the top 5 accounts based on the number of cases submitted each period. The idea I've had is to create a pivot table on a separate sheet (called "T5") and to reference the relevant cells on the main worksheet (called "Report"), so when the filter is changed the top 5 companies will change. This all works fine, but what I'm looking to do is when the data validation list is changed on the "Report" sheet, then the pivot table filter on the "T5" sheet is updated.

The second issue is that I also have a pivot table showing YTD top 5 accounts, so I also want the filter to change based on the data validation list, but I want the filter to show all YTD periods, so if 1 is selected from the validation list, then only P1 is shown, if 2 is selected, then both P1 and P2 combined, and so on.

I've tried looking online but tbh I'm a little bit unsure of where to start! Can anyone point me in the right direction???

If you need any more information then please let me know.

Thanks in advance for any help!!

:)
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

kidwispa

Active Member
Joined
Mar 7, 2011
Messages
330
Can anyone help with this?

Just to clarify what I want to achieve is when the value in cell D2 on the sheet called "Report" is changed the report filter on the pivot tables on sheet "T5" is changed to match it. So when D2 is changed to 1, the pivot table report filter on the two pivot tables is changed to 1.

Thanks again.

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,550
Messages
5,625,450
Members
416,107
Latest member
AVaes

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