#REF error!

Discussion in 'Microsoft Excel Worksheet Functions' started by Tom, Sep 19, 2009.

  1. Tom

    Tom Guest

    Excel 2007 SP2+

    I'm getting a #REF error on this:
    =IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
    ROW())),"")

    I've tried several ideas... none of them work to resolve the #REF errors.
    SOME ideas I've tried: Format to be General, Format to be Number (0 decimal
    places), Format to be Text for the D$1 cell. The format on the RawData!
    cells are numeric.
    In stepping through the calculations, everything appears to work fine except
    for the final calculation on
    "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),"). Once THIS calculation is
    performed I receive the #REF error message. I receive
    IF(TRUE,(SUM(#REF!),""). So, the issue is evidently relating to the RawData
    cells and/or the format of the cell types or ????.
    Thanks!

    ANY assistance would be appreciated. The format for the cells to store the
    SUM calculated values are numeric.
    I'm totally lost! ;-(
     
    Tom, Sep 19, 2009
    #1
    1. Advertisements

  2. On Sat, 19 Sep 2009 09:38:01 -0700, Tom
    <> wrote:

    >=IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
    >ROW())),"")



    Try to remove the second "RawData!", like this:

    =IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &
    ROW())),"")

    Hope this helps / Lars-Åke
     
    Lars-Åke Aspelin, Sep 19, 2009
    #2
    1. Advertisements

  3. 1) now need for sheet refernce twice:
    =SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &ROW()))
    2) if D1 has value 4, this formula will give REF error in any row less than
    4 since the expression Row()-4+1 will evaluate to a negative number in rows
    prior to 4
    best wishes
    --
    Bernard Liengme
    Microsoft Excel MVP
    http://people.stfx.ca/bliengme

    "Tom" <> wrote in message
    news:...
    > Excel 2007 SP2+
    >
    > I'm getting a #REF error on this:
    > =IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
    > ROW())),"")
    >
    > I've tried several ideas... none of them work to resolve the #REF errors.
    > SOME ideas I've tried: Format to be General, Format to be Number (0
    > decimal
    > places), Format to be Text for the D$1 cell. The format on the RawData!
    > cells are numeric.
    > In stepping through the calculations, everything appears to work fine
    > except
    > for the final calculation on
    > "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),"). Once THIS calculation
    > is
    > performed I receive the #REF error message. I receive
    > IF(TRUE,(SUM(#REF!),""). So, the issue is evidently relating to the
    > RawData
    > cells and/or the format of the cell types or ????.
    > Thanks!
    >
    > ANY assistance would be appreciated. The format for the cells to store
    > the
    > SUM calculated values are numeric.
    > I'm totally lost! ;-(
    >
     
    Bernard Liengme, Sep 19, 2009
    #3
  4. Tom

    Tom Guest

    Thanks for pointing this out!
    Much appreciation!

    "Bernard Liengme" wrote:

    > 1) now need for sheet refernce twice:
    > =SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &ROW()))
    > 2) if D1 has value 4, this formula will give REF error in any row less than
    > 4 since the expression Row()-4+1 will evaluate to a negative number in rows
    > prior to 4
    > best wishes
    > --
    > Bernard Liengme
    > Microsoft Excel MVP
    > http://people.stfx.ca/bliengme
    >
    > "Tom" <> wrote in message
    > news:...
    > > Excel 2007 SP2+
    > >
    > > I'm getting a #REF error on this:
    > > =IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
    > > ROW())),"")
    > >
    > > I've tried several ideas... none of them work to resolve the #REF errors.
    > > SOME ideas I've tried: Format to be General, Format to be Number (0
    > > decimal
    > > places), Format to be Text for the D$1 cell. The format on the RawData!
    > > cells are numeric.
    > > In stepping through the calculations, everything appears to work fine
    > > except
    > > for the final calculation on
    > > "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),"). Once THIS calculation
    > > is
    > > performed I receive the #REF error message. I receive
    > > IF(TRUE,(SUM(#REF!),""). So, the issue is evidently relating to the
    > > RawData
    > > cells and/or the format of the cell types or ????.
    > > Thanks!
    > >
    > > ANY assistance would be appreciated. The format for the cells to store
    > > the
    > > SUM calculated values are numeric.
    > > I'm totally lost! ;-(
    > >

    >
     
    Tom, Sep 19, 2009
    #4
  5. Tom

    Tom Guest

    THANKS, Lars, how did you know that?
    How can I get up to your level of expertise?
    Tom

    "Lars-Ã…ke Aspelin" wrote:

    > On Sat, 19 Sep 2009 09:38:01 -0700, Tom
    > <> wrote:
    >
    > >=IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
    > >ROW())),"")

    >
    >
    > Try to remove the second "RawData!", like this:
    >
    > =IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &
    > ROW())),"")
    >
    > Hope this helps / Lars-Ã…ke
    >
    >
    >
     
    Tom, Sep 19, 2009
    #5
  6. Tom

    Tom Guest

    Barry, thanks...
    Now when do I use INDIRECT vs OFFSET vs INDEX then?
    How'd you learn these?

    "barry houdini" wrote:

    >
    > and unless you have a good reason to use INDIRECT then it might be
    > better to use OFFSET or INDEX, i.e.
    >
    > =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
    >
    >
    > --
    > barry houdini
    > ------------------------------------------------------------------------
    > barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
    > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=136395
    >
    >
     
    Tom, Sep 19, 2009
    #6
  7. Tom,
    Here is an example of where INDIRECT could not be replaced by OFFSET:
    Let A1:A10 hold a list of sheet names, and we want to pick values from D10
    on each sheet
    We could use =INDIRECT(A1&"!D10") or to be more careful (sheet names could
    have spaces in them and need to be enclosed in single quotes)
    =INDIRECT("'"&A1&"'!D10")
    Either formula could eb copried down the column
    best wishes

    "Tom" <> wrote in message
    news:...
    > Barry, thanks...
    > Now when do I use INDIRECT vs OFFSET vs INDEX then?
    > How'd you learn these?
    >
    > "barry houdini" wrote:
    >
    >>
    >> and unless you have a good reason to use INDIRECT then it might be
    >> better to use OFFSET or INDEX, i.e.
    >>
    >> =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
    >>
    >>
    >> --
    >> barry houdini
    >> ------------------------------------------------------------------------
    >> barry houdini's Profile:
    >> http://www.thecodecage.com/forumz/member.php?userid=72
    >> View this thread:
    >> http://www.thecodecage.com/forumz/showthread.php?t=136395
    >>
    >>
     
    Bernard Liengme, Sep 19, 2009
    #7
  8. Tom

    T. Valko Guest

    Just to add my 2 cents....

    >=IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")


    The whole of the formula is based on what row the formula is entered on. If
    the OP doesn't provide that info then it's pretty hard to pinpoint the
    problem but a #REF! error would usually mean OFFSET is not using a valid
    reference.

    In general I don't like using ROW() or COLUMN() with no argument. It's safer
    to use ROWS(...) and COLUMNS(...).

    --
    Biff
    Microsoft Excel MVP


    "Bernard Liengme" <> wrote in message
    news:...
    > Tom,
    > Here is an example of where INDIRECT could not be replaced by OFFSET:
    > Let A1:A10 hold a list of sheet names, and we want to pick values from
    > D10 on each sheet
    > We could use =INDIRECT(A1&"!D10") or to be more careful (sheet names could
    > have spaces in them and need to be enclosed in single quotes)
    > =INDIRECT("'"&A1&"'!D10")
    > Either formula could eb copried down the column
    > best wishes
    >
    > "Tom" <> wrote in message
    > news:...
    >> Barry, thanks...
    >> Now when do I use INDIRECT vs OFFSET vs INDEX then?
    >> How'd you learn these?
    >>
    >> "barry houdini" wrote:
    >>
    >>>
    >>> and unless you have a good reason to use INDIRECT then it might be
    >>> better to use OFFSET or INDEX, i.e.
    >>>
    >>> =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
    >>>
    >>>
    >>> --
    >>> barry houdini
    >>> ------------------------------------------------------------------------
    >>> barry houdini's Profile:
    >>> http://www.thecodecage.com/forumz/member.php?userid=72
    >>> View this thread:
    >>> http://www.thecodecage.com/forumz/showthread.php?t=136395
    >>>
    >>>
     
    T. Valko, Sep 19, 2009
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hglamy

    Change #REF-Error into #NV-Error for array-formula

    hglamy, Nov 25, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    218
    hglamy
    Nov 25, 2003
  2. laneman

    #REF! error during copy and paste

    laneman, Mar 28, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    986
    Gord Dibben
    Mar 28, 2004
  3. Guest

    #REF error

    Guest, Nov 1, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    158
    Frank Kabel
    Nov 3, 2004
  4. David Bateman

    REF# error

    David Bateman, Feb 10, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    129
    Harlan Grove
    Feb 10, 2005
  5. Guest

    sumproduct function returns #value or #ref error

    Guest, Jun 24, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    195
    Leo Heuser
    Jun 24, 2005
Loading...

Share This Page