+ Reply to Thread
Results 1 to 36 of 36

Using Offset with named ranges

Hybrid View

  1. #1
    StanJ
    Guest

    Using Offset with named ranges

    I am using naming conventions for rows and trying to reference cell values
    within each row for a calculation. For example:

    =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)

    The formula should return the subtracted difference of two cells from
    another worksheet (ex: Sheet1$E$69-Sheet1$C$69). The first segment works
    fine. The second segment offset (-2 columns) does not, it returns #REF!.
    However, moving the calculation up rows [OFFSET(namerange,-2,0,,)] does work.

    Any suggestions?

  2. #2
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


  3. #3
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  4. #4
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  5. #5
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  6. #6
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  7. #7
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  8. #8
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  9. #9
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  10. #10
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  11. #11
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  12. #12
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  13. #13
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  14. #14
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  15. #15
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  16. #16
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > I think I see your point now. I am naming entire rows that are out of
    > range when looking for -2 columns.


    so, if your column C is "myrange", you are in row 14 and you want to
    go -2 colums - and you definetily think you need the offset command in
    your case (think about it!!!) then you could write into B14:

    =offset(myrange,row(b14)-1, -2, 1, 1)

    arno


  17. #17
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  18. #18
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  19. #19
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  20. #20
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  21. #21
    StanJ
    Guest

    Re: Using Offset with named ranges

    I think I see your point now. I am naming entire rows that are out of range
    when looking for -2 columns. I assumed that the named range would carry the
    logic of address with it as if I were referencing the cell (=namerange) which
    would give me the cell's value that corresponds with exact column placement
    on a different worksheet.

    It still puzzles me why the =offset(namerange,0,0,,) would work as if I just
    typed =namerange but that =offset(namerange,0,-2,,) will not.

    "arno" wrote:

    > Hi StanJ,
    >
    > > The normal cell reference (=e69-c69) returns the correct answer.

    >
    > ok.
    >
    > what ist the adress of the top-left-cell of range "namerange"?
    > what ist the result of
    > =OFFSET(namerange,0,0,,)
    > and
    > =OFFSET(namerange,0,-2,,)
    > and
    > =row(namerange)
    > and
    > =column(namerange)
    >
    >
    > arno
    >


  22. #22
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  23. #23
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  24. #24
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  25. #25
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  26. #26
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > The normal cell reference (=e69-c69) returns the correct answer.


    ok.

    what ist the adress of the top-left-cell of range "namerange"?
    what ist the result of
    =OFFSET(namerange,0,0,,)
    and
    =OFFSET(namerange,0,-2,,)
    and
    =row(namerange)
    and
    =column(namerange)


    arno

  27. #27
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  28. #28
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  29. #29
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  30. #30
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  31. #31
    StanJ
    Guest

    Re: Using Offset with named ranges

    The normal cell reference (=e69-c69) returns the correct answer. I am working
    on a cash flow statement calculating the difference between AR in this
    instance:

    Column E = 9,368.3
    Column C = 12,037.9
    Difference = -2,669.6

    "arno" wrote:

    > Hi StanJ,
    >
    > > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    > >
    > > The first segment
    > > works fine. The second segment offset (-2 columns) does not, it
    > > returns #REF!

    >
    > What is the content of the cell that is in the same row and two
    > columns to the left of the cell in the upper left corner of range
    > "namerange"? I think your problem has nothing to do with offset, it's
    > got to do with what the contents of cells are that you are referring
    > to. Replace your offset-functions with normal references like =a1-b1
    > and see if the error still occurs.
    >
    > arno
    >
    >


  32. #32
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


  33. #33
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


  34. #34
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


  35. #35
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


  36. #36
    arno
    Guest

    Re: Using Offset with named ranges

    Hi StanJ,

    > =OFFSET(namerange,0,0,,) - OFFSET(namerange,0,-2,,)
    >
    > The first segment
    > works fine. The second segment offset (-2 columns) does not, it
    > returns #REF!


    What is the content of the cell that is in the same row and two
    columns to the left of the cell in the upper left corner of range
    "namerange"? I think your problem has nothing to do with offset, it's
    got to do with what the contents of cells are that you are referring
    to. Replace your offset-functions with normal references like =a1-b1
    and see if the error still occurs.

    arno


+ 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