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?

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

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

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

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

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