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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top