Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: User defined functions

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    RET,

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    :: Pharma Z - Family drugstore ::

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •