Offset Problems

  • Thread starter Thread starter Confused
  • Start date Start date
C

Confused

I am having problems with offset.

If I enter =offset(opportunities,0,0,100,1) where
opportunities is a name defining a 100 row by n range in
another sheet in the same workbook i get a VALUE error.
=offset(opportunities,0,0,1,1) returns the leftmost cell
of the range ok, but of course it's hard to copy down.

I am also seeing an odd effect where using offset to
reference a range seems to get confused and use the row
number of the cell where the formula is coded as the
anchor row ...

Has anyone else seen thes eproblems, or am I just
completely messed up in my understanding?

Thanks
 
Try: =OFFSET(opportunities,0,0,100,n)

where n = the # of columns in the named range
"opportunities"

I've not encountered the odd effect mentioned (yet!)
 
Confused said:
I am having problems with offset.

If I enter =offset(opportunities,0,0,100,1) where
opportunities is a name defining a 100 row by n range in
another sheet in the same workbook i get a VALUE error.
=offset(opportunities,0,0,1,1) returns the leftmost cell
of the range ok, but of course it's hard to copy down.

I am also seeing an odd effect where using offset to
reference a range seems to get confused and use the row
number of the cell where the formula is coded as the
anchor row ...

Has anyone else seen thes eproblems, or am I just
completely messed up in my understanding?

Thanks
Click to expand...

=OFFSET(opportunities,0,0,100,1)
returns an array of values 100 rows long, not a single value. You can see
this by highlighting the formula in the formula bar and pressing F9. That is
why the formula is returning different values depending on which row you
enter it in. You should only be using such a formula as an array formula
which you must array-enter into an array of cells to correctly return
multiple values.

In contrast,
=OFFSET(opportunities,0,0,1,1)
returns just a single value (the top left cell of "opportunities"), so it is
quite permissible to use such a formula in a single cell.
If you want to be able to copy this down, you must use as the first
parameter (the row offset) something that will calculate the row offset for
each line. For example, if you are going to type the formula in a cell in
row 1 and then copy down, you could use
=OFFSET(opportunities,ROW()-1,0,1,1)
However, if you were then to insert a new row 1 above the row with the first
formula in it, this formula would then be in row 2 and so would give a
different result. To avoid this, a better formula to use would be
=OFFSET(opportunities,ROW()-ROW($A$1),0,1,1)

As you do not say where you are entering the formula or exactly what you are
trying to achieve, it is difficult to be more specific about what formula to
use. However, I hope this helps to remove your confusion and start you in
the right direction to solving your problem. If you still have trouble, post
a reply with a little more information.
 
Thank you Paul. You were right on all counts. I had misunderstood the implication of the fact that OFFSET returned a reference ... and indeed had decided that using ROW()-ROW(A$n) was a way to do the copy down. Nice to see the confirmation.
 
Back
Top