Summary Report Click to Drill Down

jesseandtherippers

New Member
Joined
Oct 15, 2014
Messages
3
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Is there a way to modify this so it can accommodate "=iferror(getpivotdata(..." Formulas?

I figured it out. Thanks again, Jerry!

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top