A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pivot Table criteria



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 09:58 PM posted to microsoft.public.excel.misc
Mouimet
external usenet poster
 
Posts: 19
Default Pivot Table criteria

I'm runnung Excel 2003
I create a Pivot table to see sales, refused and Pending if days is than 45
In my data table I have a column showing 2,3,4, 40, 45, 48, etc. called "Days"

In the Pivot Table I add this field "Days" in the section: PAGE of the
Pivot table
This is my first criteria. Nobody should change this.

how can I add a formulas in this pivot table saying =Days45
I tried with the ( formulas, Calculated Field), however I can not add this
in the Page section. I received a message saying I can not add in this
section the formulas. I can add it only in the Data field.

Does someone know how I can select the Data in this area?
Thanks






  #2  
Old November 13th, 2009, 11:56 PM posted to microsoft.public.excel.misc
Matrix416
external usenet poster
 
Posts: 1
Default Pivot Table criteria


Mouimet;900235 Wrote:
I'm runnung Excel 2003
I create a Pivot table to see sales, refused and Pending if days is
than 45
In my data table I have a column showing 2,3,4, 40, 45, 48, etc. called
"Days"

In the Pivot Table I add this field "Days" in the section: PAGE of the

Pivot table
This is my first criteria. Nobody should change this.

how can I add a formulas in this pivot table saying =Days45
I tried with the ( formulas, Calculated Field), however I can not add
this
in the Page section. I received a message saying I can not add in this

section the formulas. I can add it only in the Data field.

Does someone know how I can select the Data in this area?
Thanks



Mouimet
You can't query data within a pivot table in this way unfortunately.
You can do one of 2 things
If your data is from an external source ie another spreadsheet or
Access database then you can use "MS query" to filter your data which
is great.
If your data source is in the same spreadsheet then try adding another
column that compares your 'days' to a "query cell".
Lets say this cell is "A1"
Your new column would contain a formula if("days""A1","Y",N")
So if you decide your parameter is 45 days then enter it in cell A1
your new column will display "Y" if OK or "N" if not.
This new column will now be your page header in your pivot and you
select "Y"
You can change the criteria in cell "A1" from 45 days if required then
refresh the pivot




--
Matrix416
  #3  
Old November 18th, 2009, 02:52 PM posted to microsoft.public.excel.misc
Mouimet
external usenet poster
 
Posts: 19
Default Pivot Table criteria

Thanks Matrix416
I did what you said and I use the word "over" when it is over 45 days
The only problem was when I dont have any "OVER" in the Data
the macro stop working and I get an error message.

To bypass this, I add a new line at the begining of the the data sheet with
a fictive "OVER".
This fix the problem. So if anyone have the same problem, just do this.
Thanks again Matrix41 now the Pivot table works whell

"Matrix416" wrote:


Mouimet;900235 Wrote:
I'm runnung Excel 2003
I create a Pivot table to see sales, refused and Pending if days is
than 45
In my data table I have a column showing 2,3,4, 40, 45, 48, etc. called
"Days"

In the Pivot Table I add this field "Days" in the section: PAGE of the

Pivot table
This is my first criteria. Nobody should change this.

how can I add a formulas in this pivot table saying =Days45
I tried with the ( formulas, Calculated Field), however I can not add
this
in the Page section. I received a message saying I can not add in this

section the formulas. I can add it only in the Data field.

Does someone know how I can select the Data in this area?
Thanks



Mouimet
You can't query data within a pivot table in this way unfortunately.
You can do one of 2 things
If your data is from an external source ie another spreadsheet or
Access database then you can use "MS query" to filter your data which
is great.
If your data source is in the same spreadsheet then try adding another
column that compares your 'days' to a "query cell".
Lets say this cell is "A1"
Your new column would contain a formula if("days""A1","Y",N")
So if you decide your parameter is 45 days then enter it in cell A1
your new column will display "Y" if OK or "N" if not.
This new column will now be your page header in your pivot and you
select "Y"
You can change the criteria in cell "A1" from 45 days if required then
refresh the pivot




--
Matrix416
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.