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

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