software

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(quot;Totalquot;,$A$1,quot;Departmentquot;,A3) is valid reference, but
GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A44) is invalid...
I saw in couple of places (even 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

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

Michael S wrote:
gt; When using GetPivotData in new format (field-name pairs) I can't use as a
gt; reference for a name the cell that is NOT WITHIN pivot table.
gt; Let's say I have pivot table in range A1:C4. Then
gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A3) is valid reference, but
gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A44) is invalid...
gt; I saw in couple of places (even www.contextures.com/xlPivot06.html )
gt; suggestion to use external (to pivot) cell in the formula, but it looks like
gt; GetPivotData can take only cells INSIDE pivot area as reference.
gt; Am I wrong? Please, say I am...
gt; Michael--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlThank 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, quot;110quot;, but not A44, nor $A$44.
Thanks again for your time.

Michael

quot;Debra Dalgleishquot; wrote:

gt; You can use references to cells outside the pivot table. Is there a
gt; valid department name in cell A44?
gt;
gt; Michael S wrote:
gt; gt; When using GetPivotData in new format (field-name pairs) I can't use as a
gt; gt; reference for a name the cell that is NOT WITHIN pivot table.
gt; gt; Let's say I have pivot table in range A1:C4. Then
gt; gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A3) is valid reference, but
gt; gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A44) is invalid...
gt; gt; I saw in couple of places (even www.contextures.com/xlPivot06.html )
gt; gt; suggestion to use external (to pivot) cell in the formula, but it looks like
gt; gt; GetPivotData can take only cells INSIDE pivot area as reference.
gt; gt; Am I wrong? Please, say I am...
gt; gt; Michael
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
gt;

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

Michael

quot;Debra Dalgleishquot; wrote:

gt; You can use references to cells outside the pivot table. Is there a
gt; valid department name in cell A44?
gt;
gt; Michael S wrote:
gt; gt; When using GetPivotData in new format (field-name pairs) I can't use as a
gt; gt; reference for a name the cell that is NOT WITHIN pivot table.
gt; gt; Let's say I have pivot table in range A1:C4. Then
gt; gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A3) is valid reference, but
gt; gt; GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A44) is invalid...
gt; gt; I saw in couple of places (even www.contextures.com/xlPivot06.html )
gt; gt; suggestion to use external (to pivot) cell in the formula, but it looks like
gt; gt; GetPivotData can take only cells INSIDE pivot area as reference.
gt; gt; Am I wrong? Please, say I am...
gt; gt; Michael
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
gt;

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

Michael S wrote:
gt; Agrhhhh!
gt; It was as simple as concatenating empty string at the end of the
gt; reference... Like A44amp;quot;quot;
gt; Somehow value in the pivot table cell was considered to be a string, but in
gt; A44 - to be a number...
gt;
gt; Michael
gt;
gt; quot;Debra Dalgleishquot; wrote:
gt;
gt;
gt;gt;You can use references to cells outside the pivot table. Is there a
gt;gt;valid department name in cell A44?
gt;gt;
gt;gt;Michael S wrote:
gt;gt;
gt;gt;gt;When using GetPivotData in new format (field-name pairs) I can't use as a
gt;gt;gt;reference for a name the cell that is NOT WITHIN pivot table.
gt;gt;gt;Let's say I have pivot table in range A1:C4. Then
gt;gt;gt;GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A3) is valid reference, but
gt;gt;gt;GetPivotData(quot;Totalquot;,$A$1,quot;Departmentquot;,A44) is invalid...
gt;gt;gt;I saw in couple of places (even www.contextures.com/xlPivot06.html )
gt;gt;gt;suggestion to use external (to pivot) cell in the formula, but it looks like
gt;gt;gt;GetPivotData can take only cells INSIDE pivot area as reference.
gt;gt;gt;Am I wrong? Please, say I am...
gt;gt;gt;Michael--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html