Pivot Table, Calculated Field
 

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...!

Pivot Table, Calculated Field
 

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

Select the Cycle field
On the Pivot toolbar, click PivotTablesFormulas Calculated Item
Type Variance as the name for the item
Enter the formula, e.g.: ='Annual Plan'- 'Current Forecast'
Click OK

Lackawanna52 wrote:
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...!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table, Calculated Field
 

Thanks for the info. I'll try it

"Debra Dalgleish" wrote:

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

Select the Cycle field
On the Pivot toolbar, click PivotTablesFormulas Calculated Item
Type Variance as the name for the item
Enter the formula, e.g.: ='Annual Plan'- 'Current Forecast'
Click OK

Lackawanna52 wrote:
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...!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table, Calculated Field
 

Debra, it worked great, but i'm not able to subtotal on the RowField
("Cycle") - see related post

"Debra Dalgleish" wrote:

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

Select the Cycle field
On the Pivot toolbar, click PivotTablesFormulas Calculated Item
Type Variance as the name for the item
Enter the formula, e.g.: ='Annual Plan'- 'Current Forecast'
Click OK

Lackawanna52 wrote:
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...!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html