Pivot Table Drill Down

[rainx]

Hi all,

I needed some help in pivot table. I have a 2 sheets in the workbook. Sheet 1 consist of a pivot table and Sheet 2 consist of a normal table with the cells linked to the pivot table to retrieve the values. I understand I can do a drilldown on the pivot table to show the data. However, is there a way to do so by clicking on the cells from the normal table on sheet 2 which have been linked to the values of the pivot table?

The reason of me doing this is I needed a customized table with formatting for presentation purpose and thus do not want to use a pivot table for the presentation.

Will anyone be able to help? Thanks in advance!

 

[Jerry Sullivan]

Hi Rainx,

Are you using GETPIVOTTABLE function formulas to have the normal table read the PivotTable values or some other method?

In either case, please post an example formula.

Is your normal table an Excel Table (ListObject) like you could create from the Ribbon Insert Tab > Table, or just a standard range?

 

[rainx]

Hi Rainx,

Are you using GETPIVOTTABLE function formulas to have the normal table read the PivotTable values or some other method?

In either case, please post an example formula.

Is your normal table an Excel Table (ListObject) like you could create from the Ribbon Insert Tab > Table, or just a standard range?

Hi! I disabled the Generate getpivotdata from pivot table options. So I can simply press =Pivottable!B6 in another cell, and it will link to the data in the pivot table which is in B6 on the pivottable sheet. The normal table is just standard range I created.

 

[rainx]

Anyone can help?

 

[Jerry Sullivan]

Paste this code into the Sheet Code Module of the worksheet that has your standard range of cells that reference a PivotTable.
To get to the Sheet Code Module, Right-Click on that sheet's tab > View Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 '--if the double clicked cell has a formula that evaluates to a reference to
 '    a pivottable value cell, this procedure attempts to show pivottable drilldown detail
 '    for that cell.
 
 Dim rTest As Range
 
 On Error Resume Next
 '--test if formula evaluates to a range object.
 Set rTest = Evaluate(Target.Formula)
 If rTest Is Nothing Then Exit Sub
 
 With rTest
   '--test if range object is a cell in the values area of a pivottable.
   If .Count = 1 And .PivotCell.PivotCellType = xlPivotCellValue Then
      '--if so, cancel default double-click behaviour and show drilldown.
      Cancel = True
      .ShowDetail = True
   End If
 End With
 On Error GoTo 0

End Sub