Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Datafield Caption Problem
Hello, I'm having a problem with Excel forcing "Sum of " in front of the
Caption I'm assigning to my Datafields in a Pivot Table. Please see code below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the report I don't want the users to see that "Sum of ." Thanks in advance for any help you can provide. Best Regards, Dean CODE EXCERPT Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" ) strDatafieldCaption = "Backlog Coverage, Rev (K$)" strDatafieldFormula = "(BillGrossR + BLCSDR)/1000" strNumberFormat = "#,###;-#,###;" ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption, strDatafieldFormula, True With ptDashPORTPivot.PivotFields(strDatafieldCaption) .Orientation = xlDataField .NumberFormat = strNumberFormat End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Datafield Caption Problem
You can add a space to the end of the caption:
With ptDashPORTPivot.PivotFields(strDatafieldCaption) .Orientation = xlDataField .NumberFormat = strNumberFormat .Caption = strDatafieldCaption & " " End With SaeOngJeeMa wrote: Hello, I'm having a problem with Excel forcing "Sum of " in front of the Caption I'm assigning to my Datafields in a Pivot Table. Please see code below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the report I don't want the users to see that "Sum of ." Thanks in advance for any help you can provide. Best Regards, Dean CODE EXCERPT Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" ) strDatafieldCaption = "Backlog Coverage, Rev (K$)" strDatafieldFormula = "(BillGrossR + BLCSDR)/1000" strNumberFormat = "#,###;-#,###;" ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption, strDatafieldFormula, True With ptDashPORTPivot.PivotFields(strDatafieldCaption) .Orientation = xlDataField .NumberFormat = strNumberFormat End With -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Datafield Caption Problem
Hi Debra,
It worked, THANKS! I decided to append something to the end of the Datafield name string instead because that's hidden to the user. Thanks again. -- Best Regards, Dean "Debra Dalgleish" wrote: You can add a space to the end of the caption: With ptDashPORTPivot.PivotFields(strDatafieldCaption) .Orientation = xlDataField .NumberFormat = strNumberFormat .Caption = strDatafieldCaption & " " End With SaeOngJeeMa wrote: Hello, I'm having a problem with Excel forcing "Sum of " in front of the Caption I'm assigning to my Datafields in a Pivot Table. Please see code below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the report I don't want the users to see that "Sum of ." Thanks in advance for any help you can provide. Best Regards, Dean CODE EXCERPT Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" ) strDatafieldCaption = "Backlog Coverage, Rev (K$)" strDatafieldFormula = "(BillGrossR + BLCSDR)/1000" strNumberFormat = "#,###;-#,###;" ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption, strDatafieldFormula, True With ptDashPORTPivot.PivotFields(strDatafieldCaption) .Orientation = xlDataField .NumberFormat = strNumberFormat End With -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
Problem with Calculated item in Pivot Table | Excel Discussion (Misc queries) | |||
Calculated field problem in a Pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Field Problem | Excel Discussion (Misc queries) | |||
Can I pull field as datafield or as a output from a pivot table re | Excel Discussion (Misc queries) |