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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|