Extracting data from Pivot Table

Charmwah

Board Regular
Joined
Jan 23, 2017
Messages
64
Hi all

I'm trying to automate a monthly report and all the information I need is presented within a pivot table. The pivot table contains two columns, A is Customer, B is value. The pivot table has an additional row within the customer to subdivide them into categories.

Essentially i'd like to present the Top 10 from each category, in order of the value of the category itself. I realise excel presents this feature within the pivot table itself, but I want the data extracting and placing in this automated report.

I would imagine I can do this with GETPIVOTDATA somehow, possibly by using an array function to offset from the category heading down 10 places. I'm just unclear on how to achieve this.

I guess the other option is to create a mini pivot table for each category, but the problem with this is that the order of the categories in the report needs to be defined by the overall value of the category.

Thanks in advance!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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