Extracting data from pivot table using getPivotData function

**shantanuk** · 12-24-2012, 03:27 AM

Hi,

Please find attached the excel file containing a pivot table (PivotTable1). From this table i need the list of projects in an excel macro.

The expected output is -

Proj1
Proj2
Proj3

I was trying to acheive this using the getPivotData function, but no luck. Can someone please help me?

Thanks,

Shantanu Kulkarni

**dilipandey** · 12-24-2012, 04:22 AM

Hi Shantanu,

If you look at the function.. GetPivotData, I have bolded Data part because this function fetches data not the row / column headings

Alternatively, you can use below formula to get your desired output:-

Formula:

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

**shantanuk** · 12-24-2012, 04:43 AM

Hi Dilip,

Thanks for the response.

My requirement also however is not to get the headers but the data within the header "project" and the same is not working with the getPivotData. Please refer to the expected output in my original comment.

I will try the solution you have provided and come back. Till then if you could let me know if this is possible with the getPivotData it will be great.

Thanks,

Shantanu

**dilipandey** · 12-24-2012, 07:41 AM

Please refer to the expected output in my original comment.

You original comment has below output listed as your expectation:-

The expected output is -

Proj1
Proj2
Proj3

And these all are headers only i.e. Row Headings.. the item which you see in pivot as row headings and column headings are HEADERs and getpivotdata function can not fetch them, as per my knowledge. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**shantanuk** · 12-24-2012, 08:12 AM

Hi Dilip,

Got your point now. Thanks.

Does excel provide any other simple mechanism to extract the row headings? The solution that you provided earlier does work on a simple pivot but gets very complex when used with a complex pivot table.

Thanks in advance.

Shantanu

**dilipandey** · 12-24-2012, 08:20 AM

Treat the "complex pivot table" as "collection of columns" and I guess you need to extract information from any one " column " ... correct?

Now either you can use my above formula OR you can look-up required column data based on some criteria... Hope this helps.. if not share a "complex pivot table" with your expected output. thanks.

Regards,
DILIPandey
<click on below * if this helps>