Paul has attended:
Excel Advanced course
Access Intermediate course
Access Advanced course
Pivot tables
Can I add a new column to a pivot table which will refresh when the pivot table is refreshed? For example I have a pivot table with columns showing two different fileds. I have added a column which divides the first by the second, but oit would be really helpful for this column to recalcualte which the fllter is applied, or when data in the pivot table itslf is sorted in a different way.
RE: Pivot tables
Hi Paul
I also created a pivot table example which caculates one colunm divided by another. Mine calculates the % of the row grand total.
As you say the formula doesn't update if the row labels are sorted. For example if the formula =B5/F5 is typed in it won't change if the value in B5 is moved due to sorting.
But if you create the formula stating with = then click on B5 a Getpivotdata formula is created. For example:
=GETPIVOTDATA("Salary",$A$3,"Region","East","Department","Accounts")/GETPIVOTDATA("Salary",$A$3,"Department","Accounts")
The formula is longer but allows the Accounts total to be repositioned in the pivot table. The drawback of the Getpivotdata formula needs to be entered for every line. Ok for a small number of row label values.
Something else to consider -
In the Values part of the Pivot Table field list try clicking on
Value Field Settings...
Click the tab Show value as
Select one such as % of Row
I hope this helps with your question. If not you may need to turn on the Excel Option
Formulas, Use GetPivotdata functions with pivottable references.
Regards
Doug Dunn
Best STL
