Consider this function for a second:
=IF(IF(ISERROR(GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "
&$B13&" Sum of MORT-BON-CUM"))=TRUE,"",GETPIVOTDATA(INDIRECT
($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "&$B13&" Sum of MORT-BON-CUM"))
="",D12,GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "&$B13&"
Sum of MORT-BON-CUM"))
Nasty huh?
I feel I need to create an user defined function such as:
Retrieve(Link,R1,R2,R3,R4&,R5)
instead of the garbage above.
All that messy formula above is doing is retrieving data from the pivot table.
I don't understand how I would go about creating an user defined function for this.
It must basically do the same as the messy formula, but that I would rather have the messy formula in a module out of sight with the more easy on the eyes Retrieve(sheet!D4, 2 YV 2 Sum of MORT-BON-CUM) displayed in the cell.
The reason the IF ISERROR stuff is in that messy formula is because if GETPIVOTDATA is trying to retrieve information not in the pivot table then it gives an error, but I don't want REF! coming up everywhere, instead I want 0.
I would rather have all these ifs and iserrors coming put away in an user defined function somewhere.
Thanks for your attention,
RET79
=IF(IF(ISERROR(GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "
&$B13&" Sum of MORT-BON-CUM"))=TRUE,"",GETPIVOTDATA(INDIRECT
($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "&$B13&" Sum of MORT-BON-CUM"))
="",D12,GETPIVOTDATA(INDIRECT($A$3&"A1"),$A$7&" "&D$9&" "&D$10&" "&$B13&"
Sum of MORT-BON-CUM"))
Nasty huh?
I feel I need to create an user defined function such as:
Retrieve(Link,R1,R2,R3,R4&,R5)
instead of the garbage above.
All that messy formula above is doing is retrieving data from the pivot table.
I don't understand how I would go about creating an user defined function for this.
It must basically do the same as the messy formula, but that I would rather have the messy formula in a module out of sight with the more easy on the eyes Retrieve(sheet!D4, 2 YV 2 Sum of MORT-BON-CUM) displayed in the cell.
The reason the IF ISERROR stuff is in that messy formula is because if GETPIVOTDATA is trying to retrieve information not in the pivot table then it gives an error, but I don't want REF! coming up everywhere, instead I want 0.
I would rather have all these ifs and iserrors coming put away in an user defined function somewhere.
Thanks for your attention,
RET79