+ Reply to Thread
Results 1 to 36 of 36

Using Offset with named ranges

  1. #1
    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
    >


  2. #2
    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


  3. #3
    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

  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,

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


  6. #6
    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


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

  9. #9
    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
    >
    >


  10. #10
    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


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


  13. #13
    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

  14. #14
    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
    >
    >


  15. #15
    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


  16. #16
    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
    >
    >


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

  19. #19
    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


  20. #20
    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


  21. #21
    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


  22. #22
    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
    >
    >


  23. #23
    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
    >


  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,

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


  26. #26
    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
    >


  27. #27
    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

  28. #28
    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


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


  31. #31
    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


  32. #32
    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
    >


  33. #33
    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

  34. #34
    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
    >
    >


  35. #35
    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?

  36. #36
    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


+ 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