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 :wink:

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 :wink:

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