User defined functions

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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
 
Upvote 0
RET,

be careful when you hit the edges of your worksheet...... those relative references wrap around to the opposite edge (UP/DOWN, LEFT/RIGHT)
 
Upvote 0
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???
 
Upvote 0
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

:)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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,,
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top