Pivot Table, Calculated Field

  • Thread starter Thread starter Lackawanna52
  • Start date Start date
L

Lackawanna52

I have a RowField ("Cycle") with two items ("Annual Plan", "Current
Forecast") and a ColumnField with four items (Q1, Q2, Q3 and Q4). I want to
add a third RowField item called "Variance" and have it be the difference
between the Annual Plan and the Current Forecat for each ColumnField.
1. How do i add the new RowField item via VB?
2. How do i add the calculation formula via VB?

Thanks for all your help...!
 
Use the macro recorder to record the steps as you do the following:

Select the Cycle field
On the Pivot toolbar, click PivotTables>Formulas> Calculated Item
Type Variance as the name for the item
Enter the formula, e.g.: ='Annual Plan'- 'Current Forecast'
Click OK
 
Thanks for the info. I'll try it

Debra Dalgleish said:
Use the macro recorder to record the steps as you do the following:

Select the Cycle field
On the Pivot toolbar, click PivotTables>Formulas> Calculated Item
Type Variance as the name for the item
Enter the formula, e.g.: ='Annual Plan'- 'Current Forecast'
Click OK
Click to expand...
 
Debra, it worked great, but i'm not able to subtotal on the RowField
("Cycle") - see related post
 
You must log in or register to reply here.

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

Similar Threads

Remove all column and row fields from a pivot table 1
Avoiding an extra sheet when creating a pivot table 3
pivot table calculated field with user function 2
Pivot Table Deselect all items 2
Pivot table 2
Pivot Table: Optional Page Field 1
How to determine order of row fields in pivot table 1
"Unable to get Pivot Tables Property of Worksheet Class "Error 2

Back
Top