Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 791
Default 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.




  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Data Problem Dave S Excel Discussion (Misc queries) 4 November 9th 06 03:23 PM
Pivot Table Data Source external excel file IH Excel Discussion (Misc queries) 4 November 4th 06 03:42 AM
OLAP Pivot table - How to show items with no data ? Timmo Excel Worksheet Functions 1 March 30th 06 06:03 PM
Pivot table, dynamic data formula Excel GuRu Excel Discussion (Misc queries) 3 May 3rd 05 10:45 PM
How do I sort a pivot table with 2 data rows desending? vot Excel Discussion (Misc queries) 0 April 29th 05 07:00 PM


All times are GMT +1. The time now is 03:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"