Pivot Table criteria


M

Mouimet

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 =Days<45
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
 
Ad

Advertisements

M

Matrix416

Mouimet;900235 said:
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. calle
"Days"

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

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

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

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 o
Access database then you can use "MS query" to filter your data whic
is great.
If your data source is in the same spreadsheet then try adding anothe
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 A
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 yo
select "Y"
You can change the criteria in cell "A1" from 45 days if required the
refresh the pivo
 
Ad

Advertisements

M

Mouimet

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top