Pivot Table Drill Down

rainx

Board Regular
Joined
Jul 4, 2008
Messages
210
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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Jul 4, 2008
Messages
210
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.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top