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!!
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!!
