Pivot Table functionality

  • Thread starter Thread starter CClinton
  • Start date Start date
C

CClinton

Hi All,



I have 2 worksheets, one holds 8K or so records of work. this includes
things like "Whose owns the work, the date the work came in and the type of
work" plus a couple of other columns.

The seconds worksheet holds a table that summarizes the first worksheet a
little like a pivot table but using SumProducts to get the data that I need
as a pivot table would not do all the things I needed it to do.

to give you some idea on the side there are 64K formulas in the second
worksheet breaking down and summarising every little detail of the first
worksheet.

What my users want is to be able to click onto one of the cells with the
summarised data and it then pop's up the related data in a new worksheet "as
they would if it was a pivot table".

Can anyone point me in the right direction on how to do this?

Many Thanks

Mark
 
In the absence of another reply ........

1. Looks like you will need a macro.
2. Depends how your data is set up. PTs we know about.
3. A method I have used :-
a. Record a macro of a doubleclick in a pivot table to get the basi
code.
b. Set up a hidden column of PT keys in your report (say column A)
c. Make a double_click macro to refer to column A in the same row an
find/extract the Pivot Table data.

Here is some code (untested because I have removed some lines to leav
the "bones").

'----------------------------------------------
Sub GET_DETAIL()
Set DataSheet = ThisWorkbook.Worksheets("Data")
'===================
'- Active Sheet
'===================
r = ActiveCell.Row
'- check valid selection
PivotLookup = ActiveSheet.Cells(r, 1).Value
If PivotLookup = "" Then
Beep
MsgBox ("Not a valid selection.")
End
End If
'============================
'- get data from pivot table
'============================
DataSheet.Activate
Set MyTable = DataSheet.PivotTables(1)
Set FoundCell = ActiveSheet.Cells.Find(PivotLookup
ActiveSheet.Range("A1"))
If FoundCell Is Nothing Then
Beep
MsgBox (PivotLookup & vbCr & "not found in Pivot Table")
End
End If
FoundCell.Offset(0, 1).Select ' select a data cell
Selection.ShowDetail = True
End Sub
'---- end -------------------------------------------------
 
Back
Top