GetPivotData refer to external cell


G

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
 
Ad

Advertisements

D

Debra Dalgleish

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

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
 
G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top