GetPivotData refer to external cell

[Guest]

When using GetPivotData in new format (field-name pairs) I can't use as a
reference for a name the cell that is NOT WITHIN pivot table.
Let's say I have pivot table in range A1:C4. Then
GetPivotData("Total",$A$1,"Department",A3) is valid reference, but
GetPivotData("Total",$A$1,"Department",A44) is invalid...
I saw in couple of places (even http://www.contextures.com/xlPivot06.html )
suggestion to use external (to pivot) cell in the formula, but it looks like
GetPivotData can take only cells INSIDE pivot area as reference.
Am I wrong? Please, say I am...
Michael

 

[Debra Dalgleish]

You can use references to cells outside the pivot table. Is there a
valid department name in cell A44?

 

[Guest]

Thank you very much for the reply, Debra.
The point is, I read at your website that I can do it, but... it doesn't
work. I'm working with 2003 and as soon as I refer to the cell outside of
pivot used in the function - REF! I moved pivot table to the same worksheet -
doesn't help. Yes, I'm sure that A44 is valid AND that the value IS PRESENT
in current pivot table... Let's say it's 110 (as is the value of A3. So, I
can use A3, $A$3, "110", but not A44, nor $A$44.
Thanks again for your time.

Michael

 

[Guest]

Agrhhhh!
It was as simple as concatenating empty string at the end of the
reference... Like A44&""
Somehow value in the pivot table cell was considered to be a string, but in
A44 - to be a number...

Michael

 

[Debra Dalgleish]

Glad you solved the problem, and thanks for describing what you did.