User defined functions

[RET79]

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

[Jay Petrulis]

On 2002-04-16 04:50, RET79 wrote:
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

Before starting on a UDF, try writing a named formula.

Since the GetPivotData part of the formula is lengthy and referenced either with INDIRECT or absolute referencing, you can name the formula to something much shorter and place the GET PIVOT... in the Refers to box.

Then, you can have IF(ISERROR(PivotFormula),0,PivotFormula) or something like that.

One added benefit to this approach is that if it is an array formula, you won't have to array enter the named formula equivalent for this to work.

Try this route before writing the UDF.

HTH,
Jay

[RET79]

Jay,

thanks for your fantastic help. I like the fact that even when you copy the formula across to other cells, the relative referencing changes too just as would happen with an usual worksheet function.

RET79

[Chris Davison]

RET,

be careful when you hit the edges of your worksheet...... those relative references wrap around to the opposite edge (UP/DOWN, LEFT/RIGHT)

[Jack in the UK]

Hi Chris

The NASTy formul looks OK, i would not like to convert to UDF that lot, could Name define it to =JACK of cause, saves typing.

I have veiwed teh formua looks OK i cant test no Excel STILL! do you think we understand wrapping that this will be an issue... poss not in this case but could easy be so i agree,,

then results will be pear shaped... what do you think???

[Chris Davison]

Hey Jack,

yup, we can't really tell unless we know where it might be copied to..... but the unanchored "D" could cause problems if it's copied more than 3 cells left-wards, it would go : C, B, A then AV at which point the wrapping kicks in...

Just a cautionery posting in case some pear-shaped results start coming back

[RET79]

Chris,

You make a very good point.

However, in my case, the function will be 'contained' as the D's refer to column and row headings on my sheet.

Thanks anyway, and a good point to remember for the future!

RET79

[Chris Davison]

Are you sure RET ?!

your D$9's when copied left will become C$9's, then B$9's then A$9's.... copy it left once more and it may become AV$9......

try it, just to be safe !!

Hopefully you're right and it won't be an issue, but when I saw it, it rang alarm bells.

[Jack in the UK]

Hi

you ok now, still suggest =JACk and inset name and add this fornula and so maje lazy type, needs editing to suite UDF i love, but are hard going and knacker a shhet to very slow very fast,,