Pivot Table Linking
 

Hi
I am hoping someone can help me with this problem.
I am quite new to using excel and tend to learn from working through
things on my own and using tips on site like this to try and point me
in the right direction.
I have managed to create some pivot tables and several rows below this
I have a table feeding from this information which ultimately feeds
through to a summary page. My problem is that when the pivot table
increases or decrease in rows numbers (it won't ever increase in column
numbers) the table below does not recognise this.
I have not fixed the formula in the lower tables,
Is there any way I can create this link to avoid the need to manually
update the formula in the lower table.
Thanks in advance for any help you can all give.
WeeClaire


--
weeclaire
------------------------------------------------------------------------
weeclaire's Profile: http://www.excelforum.com/member.php...o&userid=28738
View this thread: http://www.excelforum.com/showthread...hreadid=485803

Pivot Table Linking
 

Hi Claire

Instead of using references to cells in the table, where the table keeps
changing in size and giving you problems, try using the GETPIVOTDATA
function instead. This directly references the Pivot table itself for Row
and Column position, and should avoid the problem you are experiencing.

Take a look at Debra Dalgleish's site for more help on this
http://www.contextures.com/xlPivot06.html

Regards

Roger Govier


weeclaire wrote:
Hi
I am hoping someone can help me with this problem.
I am quite new to using excel and tend to learn from working through
things on my own and using tips on site like this to try and point me
in the right direction.
I have managed to create some pivot tables and several rows below this
I have a table feeding from this information which ultimately feeds
through to a summary page. My problem is that when the pivot table
increases or decrease in rows numbers (it won't ever increase in column
numbers) the table below does not recognise this.
I have not fixed the formula in the lower tables,
Is there any way I can create this link to avoid the need to manually
update the formula in the lower table.
Thanks in advance for any help you can all give.
WeeClaire

Pivot Table Linking
 

Thanks Roger

I've given this a go however still having a few problems, upon typing
in the formula I am getting N/A.

As an example the pivot table I'm testing on is held in cells S4 to
Y45.
It is the sum of adj princ which is returned in the main body, the
column headers are BNDT, BOXT, CDST, DEPP, (blank), grand total. The
rows can change but there are some standard rows for example UK
GOVERNMENT GILTS, this is one of the rows I am trying to extract data
from.

The formula I have typed is:
=GETPIVOTDATA($s$4,"UK GOVERNMENT GILTS ADJ PRINC")

I am using excel 2000.

Regards
WeeClaire :confused:


--
weeclaire
------------------------------------------------------------------------
weeclaire's Profile: http://www.excelforum.com/member.php...o&userid=28738
View this thread: http://www.excelforum.com/showthread...hreadid=485803

Pivot Table Linking
 

Hi Claire

I wonder whether it is getting confused because of the spaces in Uk
Government Gilts.

Try
=GETPIVOTDATA($s$4,"UK GOVERNMENT GILTS" &" " & "ADJ PRINC ")
If that doesn't work, you can email me a copy of the file direct if you like
and I will take a look. Remove NOSPAM from my email address to send direct.


Regards

Roger Govier


weeclaire wrote:
Thanks Roger

I've given this a go however still having a few problems, upon typing
in the formula I am getting N/A.

As an example the pivot table I'm testing on is held in cells S4 to
Y45.
It is the sum of adj princ which is returned in the main body, the
column headers are BNDT, BOXT, CDST, DEPP, (blank), grand total. The
rows can change but there are some standard rows for example UK
GOVERNMENT GILTS, this is one of the rows I am trying to extract data
from.

The formula I have typed is:
=GETPIVOTDATA($s$4,"UK GOVERNMENT GILTS ADJ PRINC")

I am using excel 2000.

Regards
WeeClaire :confused: