How do I sort pivot table data outside a pivot table
 

I want to use pivot table data outside the pivot table for further
calculations and this works with just the (=G4) option (not the getpivotdata
function). However, when I try to sort the data it just won't be sorted.

Example:
1. a pivot table consists of

2006 2007
country a 10 20
country b 20 40

2. I now copy all this into a new table using the (=A2, =A3 etc.) reference
function. I do not want to copy the pivot and paste values via "paste
special" as this would destroy the link to the original data and cause to
much update on my 20+ worksheets (if I had to manually copy values in all
sheets every time I updated the raw data).

3. I try to sort the new table, but it doesn't want to be sorted.

Question: why is this and can this be done at all? The overall aim is to
derive something from the values in the table and sort by that.

How do I sort pivot table data outside a pivot table
 

Michael,

Your formulas must have absolute references

=$G$4

instead of just

=G4

An eacy way to do that is to make your table normally, then select all the cells with references and
use the macro below.
Select option 4 when prompted. Then do your sort, and the numbers will actually sort.

HTH,
Bernie
MS Excel MVP


Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As XlCalculation
Dim RefStyle As XlReferenceType
Dim MyMsg As String
Dim myStyle As Integer

MyMsg = "1: =A1 Relative" & Chr(10) & _
"2: =A$1 Absolute Row" & Chr(10) & _
"3: =$A1 Absolute Column" & Chr(10) & _
"4: =$A$1 Absolute" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, 3, or 4...."
myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)

With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual

Select Case myStyle
Case 1
RefStyle = xlRelative
Case 2
RefStyle = xlAbsRowRelColumn
Case 3
RefStyle = xlRelRowAbsColumn
Case Else
RefStyle = xlAbsolute
End Select

For Each myCell In Intersect(Selection, Selection.SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, RefStyle)
Next myCell

.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With
End Sub


"Michael" wrote in message
...
I want to use pivot table data outside the pivot table for further
calculations and this works with just the (=G4) option (not the getpivotdata
function). However, when I try to sort the data it just won't be sorted.

Example:
1. a pivot table consists of

2006 2007
country a 10 20
country b 20 40

2. I now copy all this into a new table using the (=A2, =A3 etc.) reference
function. I do not want to copy the pivot and paste values via "paste
special" as this would destroy the link to the original data and cause to
much update on my 20+ worksheets (if I had to manually copy values in all
sheets every time I updated the raw data).

3. I try to sort the new table, but it doesn't want to be sorted.

Question: why is this and can this be done at all? The overall aim is to
derive something from the values in the table and sort by that.