How to apply filter to original data by clicking pivot cell?

[Kapil Suryawanshi]

Hello Friends,

I have been struggling with this for a while. On pivot table when I double click on cell, a new worksheet is created with the filtered data. Therefore after some time there are so many worksheets created along with the original worksheet with data.

Instead I want to have an ability such that when I click a pivot cell, the original table of data should be filtered. So that I only have one data table at the end.

Can you please let me know how to do that with the attached sample file?

Thank you,
Kapil

 

[Luke M]

Hi Kapil, and welcome to the forum!

Here's a macro that was written awhile back that will do what you ask:
http://dailydoseofexcel.com/archives/2010/11/23/my-last-post-on-filtering-pivottable-source-data/

Another method is to only have one sheet that appears when you double-click on PivotTable.
http://www.ozgrid.com/forum/archive/index.php/t-68798.html

The latter still creates a new sheet, but you can then double-click anywhere to close it, and/or double-clicking on the PT will just re-use the existing sheet.