Extract Data from a Pivotable

[pjmorris]

Dear All,

Having tried to do this with GETPIVOTDATA I now believe that that function only extracts totals and subtotals. I'm trying to extract the heirarchical data and I wonder if this is possible (I suppose I could go back to the original data, but I want to know if I can extract it from the pivot).

The source data is as follows:

company	unit	name	worker
Company	AA	Harry	Buggins
Company	AA	Harry	Arold
Shop	NN	Julie	Ken
Shop	NN	Julie	Lora
Company	CC	Martin	Nick
Company	AA	Harry	Cooper
Company	AA	Harry	Denis
Company	BB	Ian	Eric
Company	AA	Harry	Frank
Company	BB	Ian	Gertrude

<tbody>
</tbody>

The Pivot Table output looks like this:

company	unit	name	worker
Company	AA	Harry	Arold
			Buggins
			Cooper
			Denis
			Frank
	BB	Ian	Eric
			Gertrude
	CC	Martin	Nick
Shop	NN	Julie	Ken
			Lora

<tbody>
</tbody>

I would like to be able to refer to say 'Frank' and return that he works for the 'Company' company , in the 'AA' Unit, with a boss named 'Harry'. If I hover my mouse of the name Frank I get a popup that provides just that information, but I can't find a way of doing it either with code or a function; as noted above I cant make GETPIVOTDATA achieve this result.

Grateful for any advice.

Regards

 

[mart37]

When you repeat all itemlables you can use INDEX and MATCH to extract data from your Pivot Table.

 

[pjmorris]

Hi Mart,

How simple! I assumed I could do it, just didn't know how.

Many thanks, much appreciated.

Regards

Peter