Summary Report Click to Drill Down

[jesseandtherippers]

I have a report that uses two workbooks:

1. Back End - Contains data and pivots
2. Front End - Summary that displays pivot values through 'Index/Match' lookups.

Is it possible to somehow click on a value on the Front End displayed value and pull up the drill down details (like double clicking in the source pivot to show details)? Perhaps using hyperlinks or macros or a combo?

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

If you modify your Front End formulas to use the GetPivotData function instead of Index/Match then you could use the code shown here to drill down on those summary values.

http://www.mrexcel.com/forum/excel-...own-feature-used-other-cells.html#post3885180

 

[jesseandtherippers]

Jerry, this is incredible. Thank you so much.

Is there a way to modify this so it can accommodate "=iferror(getpivotdata(..." Formulas?

 

[jesseandtherippers]

Is there a way to modify this so it can accommodate "=iferror(getpivotdata(..." Formulas?

I figured it out. Thanks again, Jerry!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'--if dbl-clicked cell contains simple getPivotData formula, this sub
'  attempts to execute showdetails method to display drill down data in pivot cache
'  if sub fails to find valid pivotcell, normal dbl-click behavior occurs.
'--works by parsing formula by commas then passing arguments to getPivotData method
'  current version of code will not handle arguments that have nested commmas.
 Dim lNdx As Long
 Dim pvt As PivotTable
 Dim rDataCell As Range
 Dim sFormula As String, sWksArgs As String
 Dim sArgs() As String
 Dim vArgs As Variant
  
 '--validate double-clicked range is a single cell with a simple getpivotdata formula
 If Target.CountLarge > 1 Then GoTo ExitProc
 sFormula = Target.Formula
 If UCase$(Left(sFormula, 22)) <> "=IFERROR(GETPIVOTDATA(" Then GoTo ExitProc
 '--get simple worksheet formula arguments
 
 sWksArgs = Mid(sFormula, 23, Len(sFormula) - 26)
 '--parse arguments into array
 vArgs = Split(sWksArgs, ",")
 
 '--test that 2nd argument is valid pivotcell reference
 On Error Resume Next
 Set pvt = Me.Evaluate(vArgs(1)).PivotTable
 On Error GoTo 0
 If pvt Is Nothing Then GoTo ExitProc
 
 '--limit this sub to handle 14 field-item pairs
 '  to make compatible with xl2007 getpivotdata method
 If UBound(vArgs) > 28 Then GoTo ExitProc
 If UBound(vArgs) = 1 Then
   '--grand total with no field-item pairs
    On Error Resume Next
   Set rDataCell = pvt.GetPivotData(CStr(Evaluate(vArgs(0))))
 Else
   '--evaluate arguments and cast as strings into array
   ReDim sArgs(0 To 28)
   '--0 index is datafield
   sArgs(0) = Me.Evaluate(vArgs(0))
   '--remaining indicies in vArgs are pairs of fields-items
   For lNdx = 1 To UBound(vArgs) - 1
     sArgs(lNdx) = Me.Evaluate(vArgs(lNdx + 1))
   Next lNdx
   
   '--fill remaining indicies in sArgs with 1st fields-items pair
   For lNdx = UBound(vArgs) To 28
     If lNdx Mod 2 Then
        '--odd indicies get default field name
        sArgs(lNdx) = sArgs(1)
     Else
        '--even indicies get default item name
        sArgs(lNdx) = sArgs(2)
     End If
   Next lNdx
   On Error Resume Next
   Set rDataCell = pvt.GetPivotData(sArgs(0), sArgs(1), sArgs(2), sArgs(3), sArgs(4), _
     sArgs(5), sArgs(6), sArgs(7), sArgs(8), sArgs(9), sArgs(10), sArgs(11), sArgs(12), _
     sArgs(13), sArgs(14), sArgs(15), sArgs(16), sArgs(17), sArgs(18), sArgs(19), sArgs(20), _
     sArgs(21), sArgs(22), sArgs(23), sArgs(24), sArgs(25), sArgs(26), sArgs(27), sArgs(28))
   On Error GoTo 0
 End If
 
 If Not rDataCell Is Nothing Then
   '--cancel default dbl-click behavior
   Cancel = True
   rDataCell.ShowDetail = True
 End If
ExitProc:
   
End Sub

 

[Jerry Sullivan]

Nicely done!