excel 2003 - GETPIVOTDATA

[silentbuddha]

Hi,

I am having difficulty trying to create a cell reference within my GETPIVOTDATA formula

example :
GETPIVOTDATA("Sum of "&$C$16,ASIAN!$DD$10,"Week",DATE(D$8,D$7,D$6))

How do I create a cell reference for : ASIAN!$DD$10 ??

Thanks in advance

 

[JuanPa]

It's an old one, check this out: http://support.microsoft.com/kb/287736
Office lets you choose between using Pivottable-like references instead of cell references.
Just enable the toggle button as explained in the link, and activate/deactivate it as you need it.

 

[silentbuddha]

Hi JunaPa

Unfortunately...that MS KB article did not resolve the problem...when I point to the pivot table, the GETPIVOTDATA formula is already generated

this is an example of what the formula generates:

GETPIVOTDATA("Sum of "&$C$16,ASIAN!$DD$10,"Week",DATE(D$8,D$7,D$6))

However, I am still unable to create a reference for this portion of the GETPIVOTDATA formula : ASIAN!$DD$10

I have tried entereing the worksheet name in cell A1 ( A1 = ASIAN ) and the changing the formula ( see below ) but I still get a #ref error

GETPIVOTDATA(......,"'" & $A$1 & "'!"&$DD$10,..........)

Thanks !

 

[RoryA]

Use INDIRECT and a reference to whichever cell contains the relevant cell address.

 

[silentbuddha]

Hi Rorya,

This is what excel tells me about the formula

GETPIVOTDATA(data_field, pivot_table, [Field 1], [Item 1]......)

What i have so far :

GETPIVOTDATA("Sum of "&$C$16,ASIAN!$DD$10,"Week",DATE(D$8,D$7,D$6))


I am certain that ASIAN!$DD$10 represents the worksheet pivot table that I am pointing to, however, new worksheets can be created anytime and I dont want the user to change ASIAN!$DD$10 to EUROPE!$DD$10

In Cell A17 i typed in ASIAN
In Cell A18 i typed in EUROPE

afterwards, I tried the below formula and still not luck....

GETPIVOTDATA("Sum of " & $C$16,INDIRECT("A17"&"!"&$DD$10),"Week",DATE(D$8,D$7,D$6))

Thanks for your patience

 

[RoryA]

It would be:
GETPIVOTDATA("Sum of " & $C$16,INDIRECT("'"&A17&"'!$DD$10"),"Week",DATE(D$8,D$7,D$6))

 

[Norie]

silentbuddha

GETPIVOTTABLE has always been a wee bit difficult to work with, and sometimes the problem is as simple as an errant space, a misspelt word etc.

Have you tried removing the 'Sum of' part?

By the way unless you actually want to use other cells to create a reference to the pivot table you shouldn't need INDIRECT.

All you should need is a normal reference to a cell withing the pivot table, which is usuallly what Excel gives you automatically.

By the way what is in C16?

 

[silentbuddha]

Hi Norie & Rorya,

thank you both for being so patient...I went back to basics and did some simple exercises with the INDIRECT function to get a better grip on this particular function ... and I have found the solution ( which was staring at me the whole time )

here is my solution to make a dynamic reference to the pivot table locate on another worksheet within the same workbook.

=GETPIVOTDATA("Sum of " & $C$16,INDIRECT(A17&"!$DD$10"),"Week",DATE(D$8,D$7,D$6))

PS...Norie : in C16 is the pivot field items which i placed in the pivot data area. Basically, my pivot table layout has the all the weeks for current year in the pivot table column section. Every other pivot field item is placed in the pivot table data area.

Thanks once again

 

[silentbuddha]

Hi Norie & Rorya,

thank you both for being so patient...I went back to basics and did some simple exercises with the INDIRECT function to get a better grip on this particular function ... and I have found the solution ( which was staring at me the whole time )

here is my solution to make a dynamic reference to the pivot table locate on another worksheet within the same workbook.

=GETPIVOTDATA("Sum of " & $C$16,INDIRECT(A17&"!$DD$10"),"Week",DATE(D$8,D$7,D$6))

A17 : is the worksheet name to which the pivot table is located
$DD$10 : i assume this is the pivot table location

PS...Norie : in C16 is the pivot field items which i placed in the pivot data area. Basically, my pivot table layout has the all the weeks for current year in the pivot table column section. Every other pivot field item is placed in the pivot table data area.

Thanks once again

 

[RoryA]

Safer to include the single quotes, as I did, in case your worksheet name has spaces in it.