Looks like no one else has come back with a better idea yet.
hmmm.
With Pivot Tables I often find it is easier to tweak the
underlying data then mod the table itself. Naturally this
depends on you having access to the source data in a way
that is useful and open to such an approach.
Quick and Dirty Version II:
Any chance you can grab a copy of the source data and them
run a logic loop based on your criteria to remove the values
that don't suit?
Something like in Psuedo code;
(Dim rCEL as Range)
For Each rCEL in
Sheets("YourSheet").Range("YourRange")
if rCEL.Value ="YourCriteria" (for eg <2.4, or
>0, or ="August") Then
DoSomeThingHere (eg rCEL.ClearContents, or rCEL.Value = 0,
or rCEL.Value = "N/A")
end if
Next rCEL
Set rCEL = Nothing
I meant to have a peep in some books last night for you....
good luck
Darryl.
-----Original Message-----
From: MS Excel General Q & A List
[mailto:EXCEL-G PEACH.EASE.LSOFT.COM]On Behalf Of Andre
Wium
Sent: Monday, 16 April 2007 1:59 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: How to create a pivot with filter criteria?
Thanks Darryl,
In my case your proposal won't work, because I have many
monthly columns
(24) to include in the pivot, and I cannot create an
include/exclude
flag for each month. I would like the pivot to ignore any
amount below
a certain value, no matter where in the data range such low
values
appear. But thanks for the hint.
Regards.
Andre
-----Original Message-----
From: Darryl Collins
Sent: Monday, 16 April 2007 12:38 PM
A quick and dirty way would be to add in an extra field
(column) that
returns a flag if your criteria is met, and then you can
only show the
flagged data in the pivot table.
Naturally i am sure there is a more tidy way of doing this,
but hey...
regards
Darryl
-----Original Message-----
From: Andre Wium
Sent: Monday, 16 April 2007 12:24 PM
Hello all,
Is it possible to create a pivot that will only report data
falling in a
specific range? E.g. I want the pivot to disregard (or not
include in
the sum) any data lines where the amount is less than a
specific value.
Thanks for any advice.
Andre Wium
Systems Accountant
Commercial Services - Open Cut
Roche Mining
Ph: 07 3026 6513 / Mob: 0406850486
104 Melbourne Str, South Brisbane, 4101, Queensland
************************************************************
************************************************************
****************************************
The information contained in this email communication may be
confidential. You should only read, disclose, re-transmit,
copy, distribute, act in reliance on or commercialise the
information if you are authorised to do so. If you are not
the intended recipient of this email communication, please
notify us immediately by email to webmasterroche.com.au or reply by email direct to the sender
and then destroy any electronic or paper copy of this
message. Virus protection procedures are in place at Roche
Mining but we do not warrant that this email is virus free
and recommend that the recipient undertake their own virus
detection measures.
************************************************************
************************************************************
****************************************
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
This email and any attachments may contain privileged and
confidential information and are intended for the named
addressee only. If you have received this e-mail in error,
please notify the sender and delete this e-mail immediately.
Any confidentiality, privilege or copyright is not waived or
lost because this e-mail has been sent to you in error. It
is your responsibility to check this e-mail and any
attachments for viruses. No warranty is made that this
material is free from computer virus or any other defect or
error. Any loss/damage incurred by using this material is
not the sender's responsibility. The sender's entire
liability will be limited to resupplying the material.
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|