Can the Pivot Cache "Drill Down" feature be used in other cells?

[SakiSam]

Good Day,
For my first post I'd like to know if it is possible to use the "drill down" feature of a pivot table within a cell out side of that pivot table.


I am using Excel 2010, on Windows 7. The end user(s) will have these (at a minimum) or newer.


I have a report that tracks inventory a number of different ways using pivot tables. Because of the design requirements for the report, I cannot use the pivot tables directly in the report. As a result I use the "GETPIVOTDATA" function a lot.


The end user(s) need the ability to drill down to the details from the formatted report and not the pivot tables. Currently this is done by capturing the parameters associated with the cell and looping through the source data to find the fields to filter on. For this function, I am maintaining the source data tables/worksheets within the workbook. This procedure works really well but having the source data stored on a worksheet and in the pivot cache is a redundancy. Is there a way to recall the contents of the pivot cache and run the filter function or "drill down" on it instead?


Without any luck, I've searched for ways to recall the PivotCache without the use of any add-ins.


Thank you for assistance

 

[Chengo]

Thank you Jerry!

This did what I needed it to do.

 

[brian007]

Hi

Thanks for this ... this is fantastic seen in action !

I am trying to add an IFERROR(GETPIVOT .... to return NIL instead of #REF when no data is found ?

Is this possible ? and if yes, which lines of the code do i need to change for this to work ?

Thanks
Brian

 

[DINAUKAUR]

I'm glad to hear that you found a workaround that gives you the information you want.

I'm not following how your solution applies to your example in post #4 .

{=SUM(IFERROR(GETPIVOTDATA("Count of ID",MasterPivoted!$BA$3,"StatusCodes",C$3:N$3,"Year",TRIM($B19)),0))}

I see how you find the arguments by adjusting for the additional characters in the formula. The challenge with the formula above is that it is an array formula so the drill down needs to be the combination of the drill down ranges returned from showing details when StatusCodes=C3, D3, E3...N3. Am I correctly understanding that your solution still just gets the drilldown of one cell? Did you solve the array formula problem through grouping the records associated with C$3:N$3 into one cell of the Pivot?

Regarding your question on the Slicer, to what area of the PivotTable is the Slicer applied (Report Filter, Row Labels, Column Labels)?
Could you provide an example- typically items filtered out by a Slicer don't have their records appear in the drill down details.

Hi, Dear Mr. Excel eminent Professor, I am sorry I have forgotten to view your name. This code works for me a lot. Actually at this time I am searching every where to get accurate code. You have given an excellent code. Its really worked out for my Production Information Reports. Thanks a Lot. DINAUKAUR HYDERABAD INDIA.

<tbody>
</tbody>

 

[ssjiro]

Hello Jerry, this post is really amazing and helpful for me.
By the way, your macro is working when I double click on a cell, but it is not allowed me to do with merged cell.
When I double click on number in merged cell, I cannot find the underlying data.
Could you please please let me know how to use this macro both in a cell and merged cell?
Thanks.