Filter a new Pivot in another Worksheet

bochaton · Mar 5th, 2002, 10:55 PM

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 · Mar 5th, 2002, 11:04 PM

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 · Mar 5th, 2002, 11:08 PM

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 · Mar 5th, 2002, 11:18 PM

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

bochaton · Mar 5th, 2002, 11:29 PM

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 · Mar 5th, 2002, 11:34 PM

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. · Mar 6th, 2002, 09:02 AM

Quote:

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 10:55 PM	#1
bochaton New Member Join Date: Mar 2002 Posts: 3	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

Mar 5th, 2002, 11:04 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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"

Mar 5th, 2002, 11:08 PM	#3
bochaton New Member Join Date: Mar 2002 Posts: 3	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,

Mar 5th, 2002, 11:18 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	You have confused me even more! If your tables are seperate one will NOT effect the other.

Mar 5th, 2002, 11:29 PM	#5
bochaton New Member Join Date: Mar 2002 Posts: 3	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

Mar 5th, 2002, 11:34 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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