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!
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!
