GETPIVOTDATA problem

[Laurence Lombard]

I have the following formula

=GETPIVOTDATA(Trial!$C$3,"ACC["& D46&"]")

Trial!$C$3 is a cell in a pivot table that has a list of various ACC with
amounts. In cell D46 I have the ACC number for which I want to look up the
corresponding amount. Some ACC are whole numbers 490, 499 etc and some are
decimal eg 490.1, 480.1

Now when D46 are fractions eg 490.1 it returns the correct value, but if it
is one of the whole numbers (eg 499) I get a #REF (both the whole numbers
and fractions exist in the list of ACC.

Any help would be appreciated

Thanks
Laurence

 

[Bernie Deitrick]

Laurence,

Both whole numbers and decimal numbers worked for me. Try typing = and then selecting a data cell
on a row with a whole number ACC and see what the resulting GETPIVOTDATA formula looks like. Do it
again for a decimal value ACC, then compare the resulting formulas.

HTH,
Bernie
MS Excel MVP

 

[Laurence Lombard]

Bernie
Thanks for your reply. I have saved a file (no macros) showing the problem
here
http://mysite.mweb.co.za/residents/lombardm/getpivotdataexample.zip

Above the pivot table are two GETPIVOTDATA formulas, the one works, the
other returns a #REF.

I have deleted the source of the Pivot Table in this example, so refreshing
it should not work.

Could you please have a look and see if you can see what the problem is.
Many thanks
Laurence

Laurence,

Both whole numbers and decimal numbers worked for me. Try typing = and
then selecting a data cell on a row with a whole number ACC and see what
the resulting GETPIVOTDATA formula looks like. Do it again for a decimal
value ACC, then compare the resulting formulas.

HTH,
Bernie
MS Excel MVP

I have the following formula

=GETPIVOTDATA(Trial!$C$3,"ACC["& D46&"]")

Trial!$C$3 is a cell in a pivot table that has a list of various ACC with
amounts. In cell D46 I have the ACC number for which I want to look up
the corresponding amount. Some ACC are whole numbers 490, 499 etc and
some are decimal eg 490.1, 480.1

Now when D46 are fractions eg 490.1 it returns the correct value, but if
it is one of the whole numbers (eg 499) I get a #REF (both the whole
numbers and fractions exist in the list of ACC.

Any help would be appreciated

Thanks
Laurence

 

[Bernie Deitrick]

Laurence,

The proper syntax is

=GETPIVOTDATA("AMOUNT",$A$7,"ACC",A1)
=GETPIVOTDATA("AMOUNT",$A$7,"ACC",A2)

I'm not sure where you got the ACC[ X ] syntax:

=GETPIVOTDATA($A$7,"ACC["& A1&"]")
=GETPIVOTDATA($A$7,"ACC["& A2&"]")

Again, it is easy to generate the proper syntax: type an equal sign, and select the cell with the
data that you want. The first formula above was initially

=GETPIVOTDATA("AMOUNT",$A$7,"ACC",130)

and I simply replaced the 130 with the cell reference.

HTH,
Bernie
MS Excel MVP

Bernie
Thanks for your reply. I have saved a file (no macros) showing the problem here
http://mysite.mweb.co.za/residents/lombardm/getpivotdataexample.zip

Above the pivot table are two GETPIVOTDATA formulas, the one works, the other returns a #REF.

I have deleted the source of the Pivot Table in this example, so refreshing it should not work.

Could you please have a look and see if you can see what the problem is.
Many thanks
Laurence

Laurence,

Both whole numbers and decimal numbers worked for me. Try typing = and then selecting a data
cell on a row with a whole number ACC and see what the resulting GETPIVOTDATA formula looks like.
Do it again for a decimal value ACC, then compare the resulting formulas.

HTH,
Bernie
MS Excel MVP

I have the following formula

=GETPIVOTDATA(Trial!$C$3,"ACC["& D46&"]")

Trial!$C$3 is a cell in a pivot table that has a list of various ACC with amounts. In cell D46 I
have the ACC number for which I want to look up the corresponding amount. Some ACC are whole
numbers 490, 499 etc and some are decimal eg 490.1, 480.1

Now when D46 are fractions eg 490.1 it returns the correct value, but if it is one of the whole
numbers (eg 499) I get a #REF (both the whole numbers and fractions exist in the list of ACC.

Any help would be appreciated

Thanks
Laurence

 

[Laurence Lombard]

Bernie
I could not get your syntax to work so I read the help more carefully - I
looked up "Rules for using Pivot Table or Pivot Chart names in formulas" -
Excel 2000

The solution is - I need to put the ACC number in single quotation marks eg
=GETPIVOTDATA($A$7,"ACC['"& A1&"']")
or to use a more readable example =GETPIVOTDATA($A$7,"ACC['120']")

without single quotation marks it "refers to the item by position" according
to the help - so everytime the ACC was a whole number it referred to the
item by position (and I did not know it).

I had not worked on this formula for a long time, so had got rusty on the
syntax.

Thanks very much for your time.
Regards
Laurence

Laurence,

The proper syntax is

=GETPIVOTDATA("AMOUNT",$A$7,"ACC",A1)
=GETPIVOTDATA("AMOUNT",$A$7,"ACC",A2)

I'm not sure where you got the ACC[ X ] syntax:

=GETPIVOTDATA($A$7,"ACC["& A1&"]")
=GETPIVOTDATA($A$7,"ACC["& A2&"]")

Again, it is easy to generate the proper syntax: type an equal sign, and
select the cell with the data that you want. The first formula above was
initially

=GETPIVOTDATA("AMOUNT",$A$7,"ACC",130)

and I simply replaced the 130 with the cell reference.

HTH,
Bernie
MS Excel MVP

Bernie
Thanks for your reply. I have saved a file (no macros) showing the
problem here
http://mysite.mweb.co.za/residents/lombardm/getpivotdataexample.zip

Above the pivot table are two GETPIVOTDATA formulas, the one works, the
other returns a #REF.

I have deleted the source of the Pivot Table in this example, so
refreshing it should not work.

Could you please have a look and see if you can see what the problem is.
Many thanks
Laurence

Laurence,

Both whole numbers and decimal numbers worked for me. Try typing = and
then selecting a data cell on a row with a whole number ACC and see what
the resulting GETPIVOTDATA formula looks like. Do it again for a decimal
value ACC, then compare the resulting formulas.

HTH,
Bernie
MS Excel MVP


I have the following formula

=GETPIVOTDATA(Trial!$C$3,"ACC["& D46&"]")

Trial!$C$3 is a cell in a pivot table that has a list of various ACC
with amounts. In cell D46 I have the ACC number for which I want to
look up the corresponding amount. Some ACC are whole numbers 490, 499
etc and some are decimal eg 490.1, 480.1

Now when D46 are fractions eg 490.1 it returns the correct value, but
if it is one of the whole numbers (eg 499) I get a #REF (both the whole
numbers and fractions exist in the list of ACC.

Any help would be appreciated

Thanks
Laurence