+ Reply to Thread
Results 1 to 9 of 9

#ref! Message question

  1. #1
    Registered User
    Join Date
    02-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    16

    #ref! Message question

    I am using this function (OFFSET(C5,0,5) and as you can see, I am asking it to call the cell 5 columns back from C. Of course, that doesn't exist. So what should I have there to make it zero.

    What I am trying to do is create a spreadsheet showing when people pay me back money. I am loaning in month 1 and getting paid 5 months later. So under the repayment row, I want 0 for the first 4 months instead of #ref!.

    Thanks!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: #ref! Message question

    How about, attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: #ref! Message question

    Here is the attachment. You'll see the #ref! and #value!.

    Thanks so much.
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: #ref! Message question

    Maybe a further explanation about what should be in B10 and then C10 and so on...
    Last edited by jeffreybrown; 02-02-2013 at 03:15 PM.

  5. #5
    Registered User
    Join Date
    02-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: #ref! Message question

    B10, C10, D10 etc are when people pay me back after borrowing money. The thing is, I want to test multiple pay back periods. For example, 3 months, 6 months 18 months. The problem is, when I use 3 months for example, the first two months give me error message because they are looking at 3 months ago, which don't exist. So I need to figure out how to tell it to make it 0 if there is no cell that it's pointing to.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: #ref! Message question

    In B10 and copy right,

    =IF(COLUMNS($A10:B10) <= $B$19 + 1, 0, $B18 * $B19 / 12 * OFFSET(B5,0,-$B19) + A5)
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    02-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: #ref! Message question

    Thank you very much, it worked. I hate to be annoying, but I have no idea why that worked and I did understand the original code itself.

    Second problem is I've slightly updated the code since and it now looks like this: =($B22*($B23/12))*(OFFSET(B5,0,-$B23))+(OFFSET(B5,0,-$B23))+(OFFSET(B6,0,-$B23))

    Is there just an easy to way to say that: If this formula produces an error show me 0?

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: #ref! Message question

    You have a surfeit of parens.

    Is there just an easy to way to say that: If this formula produces an error show me 0?
    =iferror(myExpression, 0)

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: #ref! Message question

    or this...
    =IF(ISERROR(OFFSET(C5,0,5)),OFFSET($A$5,0,5),OFFSET(C5,0,5))

    you can change the OFFSET($A$5,0,5) to 0 if you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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