How to transfer all the values in a pivottable column to another table? (offset, getpivotdata)

[Wilma13]

Hi guys, </SPAN>

I have the following problem. </SPAN>

I have a main table with 6 fixed headers. From which I would like to fill the first 4 headers (columns) with data from a pivot table. The pivot table which will be used as the source also has fixed headers/columns, however the number of rows will vary/ can change.
</SPAN>
For the main table I would like column ‘A’ to show all the values listed in column ‘A’ of the ‘pivot’ table. Column 'B' to show column 'B', C-C and D-D. </SPAN>

I have made some attempt with GETPIVOTDATA (however I believe this formula is intended to return only a few cell’s not a whole column).</SPAN>
In addition I have tried to use OFFSET (here in run into the problem that some cell’s in the pivot table are empty. As such OFFSET will stop ‘selecting’ after the first empty cell, even though are filled cells after that).</SPAN>

Any help and/or suggestions are welcome. </SPAN>

 

[Andrew Poulsom]

Welcome to MrExcel.

GETPIVOTDATA returns a value not a reference so you can't OFFSET it. You can use a formula like this to get the row labels from your pivot table (assuming they don't repeat):

=INDEX(A$1:A3,MATCH(REPT("z",255),A$1:A3))

Copy the formula down.

If you are still stuck post a screenshot of your pivot table.

 

[Wilma13]

My pivot table looks as follows:

Filter: (B,2) Selling region
[TABLE="class: outer_border, width: 500"]
<TBODY>[TR]
[TD](A,5)Customer code
[/TD]
[TD](B,5)Costumer Name
[/TD]
[TD](C,6) 2013
[/TD]
[TD](D,6) 2014
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](A,6) AB6
[/TD]
[TD]Customer A
[/TD]
[TD]$1,200
[/TD]
[TD]$900
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](A,7) Z5Y
[/TD]
[TD]Customer K
[/TD]
[TD]$900
[/TD]
[TD]$1200
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](A,8) BK8
[/TD]
[TD]Customer Z
[/TD]
[TD][/TD]
[TD]$500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](A,9) ZHI
[/TD]
[TD]Customer X
[/TD]
[TD]$300
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I want the main table below to be filled with all information from the pivot table. If I select a different 'Selling region' in the filter and the pivot table changes I want the main table to automatically change and adjust its size to the number of customer listed under Customer Code.

[TABLE="class: outer_border, width: 700"]
<TBODY>[TR]
[TD](F,7) Customercode
[/TD]
[TD](G,7) Customer Name
[/TD]
[TD](H,7)2013
[/TD]
[TD](I, 7) 2014
[/TD]
[TD](J,7) variable x
[/TD]
[TD](K,7) variable Y
[/TD]
[/TR]
[TR]
[TD](F,8) AB6
[/TD]
[TD]Customer A
[/TD]
[TD]$1,200
[/TD]
[TD]$900
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[/TR]
[TR]
[TD](F,9) Z5Y
[/TD]
[TD]Customer K
[/TD]
[TD]$900
[/TD]
[TD]$1200
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](F,10) BK8
[/TD]
[TD]Customer Z
[/TD]
[TD][/TD]
[TD]$500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](F,11) ZHI
[/TD]
[TD]Customer X
[/TD]
[TD]$300
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


(A,5) = the cell number


Appologies but I was not able to place a simple screenshot. I hope I'm not asking for the impossible. Thank you for all your help!

 

[Andrew Poulsom]

Why don't you just copy the pivot table ans paste special as values and formats? Your output looks just the same.

 

[Wilma13]

Because than the main table will not automically update when I change the filter in the pivot table to a different 'selling region'. At the same time the size of the main table will not adjust to the amount of rows in the pivot table (this is important as I want to directly link the main table to powerpoint).

 

[Andrew Poulsom]

In F7 copied to G7:K7 and F8:G15:

=IF(LEN(A5),A5,"")

In H8 copied down and across:

=IF(AND(LEN($F8),LEN(H$7)),GETPIVOTDATA("Value",$A$4,$F$7,$F8,$G$7,$G8,"Year",H$7),"")