Filter a new Pivot in another Worksheet

[bochaton]

Let's say I have a Worksheet called "Data" with data (duh!), in Worksheet "PivotA" I have a pivot table based on the data in Worksheet "Data". I have another Pivot in worksheet "PivotB" which is also based on the data in worksheet "Data".

Now in PivotB I only want data where one of the fields is greater than 1000. (SalesAmount > 1000).

Can I filter out data for PivotB, while not affecting PivotA?

Thanks,
Terry

 

[Dave Hawley]

Hi Terry

Not too sure I have understood you but it sounds like you have Pivot Table "A" based off Pivot Table "B"? While this does save memory they are no longer completely seperate. Just create your Pivot Table "B" from the same data range rather than Pivot Table "A"

 

[bochaton]

Ok, I'll expand a bit let's say my data is

Salesman Sales
Terry 1000
Bob 500
Andy 250
Mary 2500

PivotA is based on all the rows,

PivotB is based on rows where Sales>500, so:
Salesman Sales
Terry 1000
Mary 2500


Thanks,

 

[Dave Hawley]

You have confused me even more! If your tables are seperate one will NOT effect the other.

 

[bochaton]

Sorry Dave, I often confuse myself . Ignore everything I've said up to now, answer this:

Is it possible in a Pivot Table to filter out data based on a formula. For example, only bring in customers that have Sales>500.

Sorry about the confusion and thanks for your quick reponses.

Terry

 

[Dave Hawley]

Arrh! In that case you should only need to place the Sales field in the inner most field and then Group it. You will only be able to do this if all you fields in sales contain numbers. In other words blanks anywhere in the Data and/or blanks rows in the Pivot data reference will prevent this. Dynamic ranges are very good for Pivot Tables and I have many examples here:

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Mark W.]

On 2002-03-05 22:08, bochaton wrote:
Ok, I'll expand a bit let's say my data is

Salesman Sales
Terry 1000
Bob 500
Andy 250
Mary 2500

PivotA is based on all the rows,

PivotB is based on rows where Sales>500, so:
Salesman Sales
Terry 1000
Mary 2500


Thanks,

Too easy!! Just add another field (column) to your data set, 'Sales>500', using the formula, =Sales>500. Configure 'Sales>500' as a PAGE field in PivotB and set its value to TRUE.
This message was edited by Mark W. on 2002-03-06 10:59