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

Epinn

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

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

HIS
www.nickhodge.co.uk

"Epinn" wrote in message
...
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