Set date filter for 2 different pivots

I don't know what MS-Office Version 8 is. But in Excel 2010 and later there is something called a slicer. If your pivot tables are built from the same data source, then you can hook up the slicer to update them both at the same time.

If the pivot tables are not built from the same data source, then some VB Coding is needed. Without a sample workbook, the best I can do is describe how this would go. First we have to ask a couple of questions. Are there dates in one data source that are not in the other? If so, is one data source all inclusive of all the dates? Or do I have to combine the data sources to get all the potential dates?

If we have to combine the data sources to get all the potential dates, I would create two "helper" pivot tables to get a list of unique dates from both tables, combine them using VBA and then get a unique list of dates by pivoting the combined list.

Then I'd use data validation against the combined unique list as the date on which to search. I then use COUNTIF to see how many times the date appears in each data source. If the COUNTIF is greater than zero, I set the filter on that table. If not, meaning that the table does not contain the date, I issue a warning and do not attempt to set the filter.

Without a sample spreadsheet, the best I can do is explain what needs to be done. Since you have the same dates, the issue is greatly simplified.

On one (or both) of the spreadsheets, use a change event on the page code to detect when you change the filter. Set the filter on the other table to the target value. This code should work assuming the cell B1 is where the filter is. This should be placed in the worksheet module. "Target" is just a fancy name for "selected cell." In this setup you don't need another dropdown list. Change the date on one spreadsheet, it will change on the other.

Please Login or Register  to view this content.

On second reading (a day later), I'm going to modify my code. If you have the pivot table filters pointing at each other then you may wind up in an infinite loop. If you change the filter on Sheet A, it changes the filter on Sheet B, but since it changed it will change the filter on Sheet A which will change the filter on Sheet B ...

To prevent this runaway condition, we need to turn off event tracking and then turn it back on.

Please Login or Register  to view this content.