Pivot table to show Filtered Source Data

[JV0710]

Hi

If I filter the data on my source data, Is it possible to have the pivot table NOT include the hidden rows resulting from the filter on the source data ?

For example: if my source data is 1000 rows and 20 columns and I filter the data based on various conditions across the columns. I want to refresh the Pivot table and have it show results without the hidden rows.

Thanks

JVN

 

[Yard]

Not without an intermediate step.

Silly question : why not just filter on your Pivot Table instead?

 

[JV0710]

Hi
You are right . . . I would normally just filter on the pivot table.
This particular pivot table that I created, though, I will be sending to a number of users to use. . . And when I was taking them through the spreadsheet and showing them how to use it, I got asked this question.

So, I thought it would be interesting to find out if it is possible. I tried using a dynamic range as the source but it does not exclude the hidden rows.

thanks for your reply

JVN

 

[Yard]

Off the top of my head you would need to use the Worksheet_Calculate event in the source data worksheet to detect if an Autofilter had been changed (not an elegant solution IMHO), then use AdvancedFilter or something to copy the visible rows to another worksheet. Your Pivot Table would then have to be based on that other worksheet.

Maybe there's a better way, but if there's no pressing need then life is too short - filter the Pivot Table!

 

[JV0710]

Agreed

Thanks