A colleague has Autofiltered a spreadsheet and then wishes to create a Pivot
Table based on the filtered data. He can create the PT fine, but ALL data is
used, not just filtered.
Is there a way around this, or a better solution?

Any assistance gratefully accepted and TIA,
Amanda

Maybe your colleague could add a field to the raw data--put in some formula that
does the equivalent of the data|filter. (Returns Show/Hide or True/False or...)

Then use that as a page field in the pivottable
Then they could use that to show/hide the data they want in the pivottable.

Birmangirl wrote:

A colleague has Autofiltered a spreadsheet and then wishes to create a Pivot
Table based on the filtered data. He can create the PT fine, but ALL data is
used, not just filtered.
Is there a way around this, or a better solution?

Any assistance gratefully accepted and TIA,
Amanda

--

Dave Peterson

Hi

Use Advanced Filter to pull the subset of data to another sheet, then
base the PT on a Dynamic range on the new sheet.

For more help on using Advanced filter in this way take a look at
http://www.contextures.com/xladvfilter01.html#ExtractWs
and for Dynamic Ranges
http://www.contextures.com/xlNames01.html#Dynamic

--
Regards

Roger Govier


"Birmangirl" wrote in message
...
A colleague has Autofiltered a spreadsheet and then wishes to create a
Pivot
Table based on the filtered data. He can create the PT fine, but ALL
data is
used, not just filtered.
Is there a way around this, or a better solution?

Any assistance gratefully accepted and TIA,
Amanda

Thanks for your suggestions Roger. I passed the information on and my
colleague has now created the PT. Using dynamic named ranges also decreased
the recalc time considerably too!

Amanda

"Roger Govier" wrote:

Hi

Use Advanced Filter to pull the subset of data to another sheet, then
base the PT on a Dynamic range on the new sheet.

For more help on using Advanced filter in this way take a look at
http://www.contextures.com/xladvfilter01.html#ExtractWs
and for Dynamic Ranges
http://www.contextures.com/xlNames01.html#Dynamic

--
Regards

Roger Govier


"Birmangirl" wrote in message
...
A colleague has Autofiltered a spreadsheet and then wishes to create a
Pivot
Table based on the filtered data. He can create the PT fine, but ALL
data is
used, not just filtered.
Is there a way around this, or a better solution?

Any assistance gratefully accepted and TIA,
Amanda