GETPIVOTDATA, a few short queries

[RET79]

Hi.

I am using the GETPIVOTDATA() function to retrieve data from my pivot tables, but am having a few little difficulties which are holding back the whole project (whose deadline is fastly approaching, so quick responses will be appreciated SO much!).

Here goes

=IF(ISERROR(GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&C$9&" "&C$10&" "
&$B11&" Sum of NUM"))=TRUE,"",GETPIVOTDATA(INDIRECT
($A$3&"A1"),$A$7&" "&C$9&" "&C$10&" "&$B11&" Sum of NUM"))

is too ugly and writing such formulas like this puts my colleagues off. The reason I need the iserror is that sometimes the data I am trying to retrieve is not there in the pivot table, so I get REF! etc. If the data is not in the pivot table then I want it to return a blank cell like the above. It works fine, but I would like to hear some suggestions about how one would construct a more 'user-friendly' and easy on the eyes version.

Next query:

=GETPIVOTDATA(A1,"6 N PP YV Sum of NUM")
&
=GETPIVOTDATA(A1,"N PP YV 6 Sum of NUM")

works but
if I add another field to the pivot table, the field being a column of numbers, then:

=GETPIVOTDATA(A1,"6 N PP 2 YV Sum of NUM")

&
=GETPIVOTDATA(A1,"2 N PP YV 6 Sum of NUM")

do NOT work. This function doesn't seem to like it when there are two fields of numbers it seems.

Can anyone help?

thanks in advance,

RET79






[ This Message was edited by: RET79 on 2002-04-08 07:07 ]

[Mark W.]

This function doesn't seem to like it when there are two fields of numbers it seems.

GETPIVOTDATA doesn't tolerate any item ambiguities -- number or text. You'll have to re-itemize one of the numeric fields. For example, if one of the fields is 'Month' use "Feb" instead of 2.

[RET79]

Thanks for the prompt reply Mark.

Unfortunately, the numbers aren't months and also they are similar numbers to that of the other column.
How could I go about setting up a non-ambiguous field? Would I have to set up a calculated field perhaps? I am not entirely sure what ambiguous means in this respect, is it ambiguous to have two columns of numbers in a pivot table or is it amiguous to have two columns of numbers with the same/similar number(s) in each, is that what you mean?

Thanks again,

RET79

[ This Message was edited by: RET79 on 2002-04-08 07:51 ]

[Mark W.]

On 2002-04-08 07:48, RET79 wrote:
Thanks for the prompt reply Mark.

Unfortunately, the numbers aren't months and also they are similar numbers to that of the other column.
How could I go about setting up a non-ambiguous field? Would I have to set up a calculated field perhaps? I am not entirely sure what ambiguous means in this respect, is it ambiguous to have two columns of numbers in a pivot table or is it amiguous to have two columns of numbers with the same/similar number(s) in each, is that what you mean?

Thanks again,

RET79

[ This Message was edited by: RET79 on 2002-04-08 07:51 ]

You item values are ambiguous as GETPIVOTDATA arguments because you can't distinguish which value belongs to which field. Change the items of 1 of the 2 fields in your data list so that they are distinct. For example, instead of...

{"Field1","Field2"
;1,2
;2,3
;3,1}

...you could use...

{"Field1","Field2"
;1,"Two"
;2,"Three"
;3,"One"}

...Or...

{"Field1","Field2"
;1,"2.0"
;2,"3.0"
;3,"1.0"}

You'll have to decide what makes sense!


[ This Message was edited by: Mark W. on 2002-04-08 08:03 ]

[RET79]

Ok, cheers again Mark, you have been excellent, much appreciated.

However, any ideas about making the initial forumla less ugly.

I like to make things look ELEGANT !!

Well, I try to anyway

RET79

[Mark W.]

On 2002-04-08 08:11, RET79 wrote:
Ok, cheers again Mark, you have been excellent, much appreciated.

However, any ideas about making the initial forumla less ugly.

I like to make things look ELEGANT !!

Well, I try to anyway

RET79

Decompose your long formula into 2 by returning the results of GETPIVOTDATA to a cell (e.g., A1). Then in B1 use the formula...

=IF(ERROR.TYPE(A1)=4,"",A1)

If you don't want to use a separate worksheet function that assign your GETPIVOTDATA function to a defined name (e.g., "Pivot_Data"), and use the formula...

=IF(ERROR.TYPE(Pivot_Data)=4,"",Pivot_Data)

[ This Message was edited by: Mark W. on 2002-04-08 08:20 ]