Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA problem
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA problem
Bernie
Thanks for your reply. I have saved a file (no macros) showing the problem here http://mysite.mweb.co.za/residents/l...ataexample.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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA problem
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 "Laurence Lombard" wrote in message ... Bernie Thanks for your reply. I have saved a file (no macros) showing the problem here http://mysite.mweb.co.za/residents/l...ataexample.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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA problem
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Laurence Lombard" wrote in message ... Bernie Thanks for your reply. I have saved a file (no macros) showing the problem here http://mysite.mweb.co.za/residents/l...ataexample.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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with getpivotdata function | Excel Discussion (Misc queries) | |||
GETPIVOTDATA Excel 2003 Problem | Excel Discussion (Misc queries) | |||
getpivotdata refresh problem | Excel Worksheet Functions | |||
GETPIVOTDATA - Pivot table name problem? | Excel Worksheet Functions | |||
GETPIVOTDATA - Pivot table name problem? | Excel Worksheet Functions |