+ Reply to Thread
Results 1 to 3 of 3

Is there a formula that allows you to reference the cell referred to in a UDF?

  1. #1
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    Is there a formula that allows you to reference the cell referred to in a UDF?

    I have a UDF that is a long if statement. Let's say my UDF based on the criteria I've set returns the contents of cell G2. Is there a formula (other then another long if statement) that will return H2 (1 cell over)?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Is there a formula that allows you to reference the cell referred to in a UDF?

    Probably some more data is needed.
    Are returned values located in one row or one column (one vector) in the sheet?
    If so you could use formula
    =index(thiscolumn,match(UDFresult,thiscolumn,0)+1) or =index(thisrow,1,match(UDFresult,thisrow,0)+1)
    if this 1 cell over is alongside vector or if in perpendicular direction you could in similar way use OFFSET

    or you could change your UDF to return already required cell
    Best Regards,

    Kaper

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Is there a formula that allows you to reference the cell referred to in a UDF?

    Impossible to say for sure without seeing the code in your UDF.

    What is the return type of your UDF?

    Note that these code samples are to illustrate the concept and I have not compiled or run them. If you provide your actual code then I would work with that.

    One option is to have it return a Range instead of a value, then you can use OFFSET in the worksheet to return the value one cell over.
    Please Login or Register  to view this content.
    Then in your worksheet

    =MyUDF(A1)

    =OFFSET(MyUDF(A1),0,1)

    Another option guaranteed to work in the general case is to write a second UDF that replicates the logic of the first but returns the next cell over. The way I would do this is to write a Private Function that returns the Range, then the UDF returns the value of that range, and your second UDF then returns the next cell over from that. This factors out the logic of the If statement into a single function, guaranteeing that the same logic is being used in both places.
    Please Login or Register  to view this content.
    Then in your worksheet

    =MyUDF(A1)

    =MyOtherUDF(A1)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. e cell referred to in a UDF?
    By Excel2010101 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2014, 01:15 AM
  2. [SOLVED] How to make a cell value dynamic when the referred value changes?
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-14-2013, 01:14 PM
  3. [SOLVED] Lookup minimum value in a column for referred Cell
    By sfshah in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-09-2012, 11:11 AM
  4. Multiple cells referred to in IF formula query
    By Danofire in forum Excel General
    Replies: 5
    Last Post: 06-24-2008, 07:00 PM
  5. [SOLVED] Conditional format but referred from other cell
    By Lamb Chop in forum Excel General
    Replies: 2
    Last Post: 07-28-2006, 04:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1