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