Different row fields for different values in pivot table?

[Ola]

I wonder if any of you excel ninjas can tell me if it is possible to to have different row fields for different value fields?


Say I have two value fields stacked (Amount & tax), can I have them preceded by different row fields?


Please have a look at the linked worksheet where I try to show want I wish to accomplish. I have coloured the wanted fields green.


https://dl.dropbox.com/u/8612596/Pivot%20problem.xlsx


TIA


Ola

 

[keymaster]

As far as I know, this is not possible in Excel pivot tables. But there is a workaround.


First create the pivot table with one row field (in your example, the pivot is already like this)

Then in copy the entire pivot, paste it else where as links (ie create references to values in pivot).

Now, edit every other row field and set it to what you want.

 

[Trader273]

You could always use the GetPivotData function. Might have to use two or more pivot table, but then just build one using the function of everything you want

 

[Ola]

Thanks, I'll look into the GetPivotData function. As I have more than 30 000 rows I would like a way to automate as much as possible. I understand making two pivot tables and then a third based on the two first and then stacking the account fields will get me close to where I want to be.


//Ola