AutoFilter and PivotTable

[Epinn]

I do AutoFilter to create a subset of data. Then I want to create a PivotTable on the *subset* and do a count. I then realize PivotTable doesn't work on the subset even though the rows are hidden. It works on the entire database. To circumvent this, I do copy/paste of the subset and create the PivotTable based on the pasted version. If the range for the subset is huge, it may not be efficient to do copy/paste. What is a more efficient way to achieve the same result?

Thanks.

Epinn

 

[Nick Hodge]

Epinn

I would us a helper column in the source data, so for example if you data
covers A11000, to filter say when column D is >20 then in D1 enter =D1>20

This will return TRUE/FALSE, copy this down the data. Include this data in
your pivot and set this column as a page field. This way you will filter
you data on TRUE or FALSE (> or <20)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

I do AutoFilter to create a subset of data. Then I want to create a
PivotTable on the *subset* and do a count. I then realize PivotTable
doesn't work on the subset even though the rows are hidden. It works on the
entire database. To circumvent this, I do copy/paste of the subset and
create the PivotTable based on the pasted version. If the range for the
subset is huge, it may not be efficient to do copy/paste. What is a more
efficient way to achieve the same result?

Thanks.

Epinn