Pivot Table double click extract/ GETPIVOTDATA()

**zonino** · 11-12-2009, 07:11 PM

Hi All,

I have a spreadsheet that pulls data through from a pivot table into a different format on a frontpage. This absolutely has to be done in this way, there isn't an alternative to this.

I am doing this by using the GETPIVOTDATA() function and this works fine, however I want to also replicate the functionality where you can double click on the pivottable result to show the lines of data that make up the result.

I know I'm going to have to use VBA for this, however I can't find the function that I need to do this..!! can anyone help?

thanks in advance

**mojo249** · 11-13-2009, 07:23 PM

Hi Zonino

Try recording a macro when you double click the pivot table. You'll get the following code:

Please Login or Register  to view this content.

Dion

**DonkeyOte** · 11-14-2009, 05:04 AM

To drill through you would (I suspect) first need to find the cell in the PT and determining the cell to which the GETPIVOTDATA function is pointing seems to me at least to be pretty complex given the flexibility inherent in PT design, ie:

a) a given field may appear multiple times in any PT
(used in row & data fields etc...)

and

b) the criteria for a given field may appear multiple times (aggregation) - establishing the correct one is thus potentially convoluted.

Now it's feasible you could find the output of the GETPIVOTDATA function within the Data Value range and then compare the various field properties of the found cell to those contained in the function itself ...
If you knew the output values were unique then it's obviously a little more straightforward but if you can't make that assumption there's a lot more work to be done.

Will Riley posted a version of the above here: http://www.ozgrid.com/forum/showthread.php?t=30296 however this method does (in present form) assume unique values.

**zonino** · 11-21-2009, 04:15 PM

Thanks for the input,

I've been working my head around this for the last few days and have come up with an alternative solution, which is actually preferable for the target audience as well (success!)

I have some VBA script that applies the autofilter function to the source data (which is all contained within the workbook), which when I find/work out code to populate my parameters (I need to work out how to grab the data from column B on the row I've clicked on, wherever I've clicked, i.e. if I click C6 or XZ6 it will still return the value for B6) will provide the functionality I need, and make it easy for people to change their mind about what they want to look at. woop