Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table help!
Is there a specific news net that deals with pivot tables? Or any web sites?
My problem is I have a calculated field that will divide a field number by a monthly constant. My page field is the Month column so I can select any of the months to display in the pivot table. However, the constant in the calculated field is different for each month. Right now I have 12 copies of the same pivot table, each displaying a different Month, then I edit the constant for each table. So I really have a variable constant for each month. Is there a way to tell the calculated field to use a certain constant depending upon which month is selected in the page field? That would let me get rid of the other 11 table copies. Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table help!
You could use a worksheet formula to return the value for the month
selected in the page field, e.g.: =VLOOKUP(B3,MonthLU,2,0) Then, use the PivotUpdate event to change the calculated field, e.g.: '========================== Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim lMonth As Long lMonth = Me.Range("MonthValue").Value Application.EnableEvents = False Me.PivotTables(Target.Name).CalculatedFields("Mont hDiv") _ .StandardFormula = "=Units/" & lMonth Application.EnableEvents = True End Sub '============================ Mike Fogleman wrote: Is there a specific news net that deals with pivot tables? Or any web sites? My problem is I have a calculated field that will divide a field number by a monthly constant. My page field is the Month column so I can select any of the months to display in the pivot table. However, the constant in the calculated field is different for each month. Right now I have 12 copies of the same pivot table, each displaying a different Month, then I edit the constant for each table. So I really have a variable constant for each month. Is there a way to tell the calculated field to use a certain constant depending upon which month is selected in the page field? That would let me get rid of the other 11 table copies. Mike F -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Print Pivot Table headers on pages with Pivot Table | Excel Worksheet Functions | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |