GetPivotData function returning N/A error for some rows

Giganews Newsgroups
Subject: GetPivotData function returning N/A error for some rows
Posted by:  Hari (excel_ha…@yahoo.com)
Date: 12 May 2006

Hi,

One of my colleagues is using the GetPivotData function to pull data
from a pivot table in another worksheet of the same workbook.

As of now the whole data is being pulled from the pivot table for
testing purposes.

The pivot has a single page field, 7 row field and 1 column field. This
pivot works just fine. We copied the structure/layout of this whole
field and then pasted in adjacent worksheet. Now, we used the
getpivotdata function to query the values. The first row field we have
is country name. For 3 countries (Italy, Germany and France) we are
getting a N/A error when we use the getpivotdata function.

If we shorten the names of these countries then the Get pivotdata works
well. But this is surprising because we have got many countries whose
name is much bigger (like Russian Federation, Tanzania United Republic
etc). Why is this happening?

Im pasting the original pivot table data for 2 countries (with headings
changed). I hope google doesnt mess this. Im also pasting the function
argument below

                            KO
Country    ID    English Name    BGT?    KIO?    ZAS?    Data    06
Austria    1-R1-1    QWERTYU AUSTRIA    Yes    Yes    No    Sum of Orders Product Units    8
                        Sum of Orders Net CLC    1,678
                        Sum of Shipts 1st Tier Product Units    1
                        Sum of Shipts 1st Tier Net CLC    -2,261
    1-R1-10    ASDFGHJ HJ    Yes    Yes    No    Sum of Orders Product Units    5
                        Sum of Orders Net CLC    577
                        Sum of Shipts 1st Tier Product Units
                        Sum of Shipts 1st Tier Net CLC
Italy    1-14E-13    MKIUYT NBVCX F.I.P.    Yes    Yes    No    Sum of Orders Product
Units    3
                        Sum of Orders Net CLC    462,924
                        Sum of Shipts 1st Tier Product Units
                        Sum of Shipts 1st Tier Net CLC
    1-14E-16    ZXCVBNH IOPLKJ ASD    Yes    Yes    No    Sum of Orders Product Units    19
                        Sum of Orders Net CLC    6,463
                        Sum of Shipts 1st Tier Product Units    17
                        Sum of Shipts 1st Tier Net CLC    5,361

Function argument for first row of Austria is
=GETPIVOTDATA(ecto,CONCATENATE($C4," ",K$3))

Austria doesnt give this error. We have copied the same formula till
the end. The function argument for Italy (which appears in row 572) is
=GETPIVOTDATA(ecto,CONCATENATE($C572," ",K$3))

ecto is a named range for the pivot table ($H$6:$Q$1497 of the pivot
worsheet) from which we are pulling the data.

Please guide me.

Regards,
HP
India

Replies