Using Offset with named ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
Click to expand...

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
 
Hi StanJ,
The normal cell reference (=e69-c69) returns the correct answer.
Click to expand...

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.
 
Hi StanJ,
I think I see your point now. I am naming entire rows that are out of
range when looking for -2 columns.
Click to expand...

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