I have a local named range called 'MyGrid'. It is a single contiguous
range.

I want to pick members of that grid using

=OFFSET(MyGrid, 0, 0, X, Y) where X & Y are worksheet references to
cells that provide the OffSet value.

This formula delivers a #VALUE error when pasted over a 2 dimensional
range.

Is my syntax incorrect?

Try this:

The way you're using the OFFSET function, it will start with the MyGrid
range, resize it to X by Y dimensions, and return the array of values in the
resized range, which Excel will display as #VALUE!.

To test that it is working as designed, enter this:

=INDEX(OFFSET(MyGrid,0,0,X,Y),1,1)
(Where X and Y are your references)

That formula should return the value in the upper left cell of the MyGrid
range.

Did you want to return a specific value within MyGrid?
If yes, use this:
=INDEX(MyGrid,X,Y)
OR
=OFFSET(MyGrid,X,Y)

(Where X and Y are your references)


Does that help?

***********
Regards,
Ron


" wrote:

I have a local named range called 'MyGrid'. It is a single contiguous
range.

I want to pick members of that grid using

=OFFSET(MyGrid, 0, 0, X, Y) where X & Y are worksheet references to
cells that provide the OffSet value.

This formula delivers a #VALUE error when pasted over a 2 dimensional
range.

Is my syntax incorrect?

Could you provide more information regarding your 2-dimensional range,
like what is in it, and also the values in X and Y? Are X and Y named
cells or are you using references and are the references absolute or
relative or mixed?

You might also let us know what you are trying to accomplish with the
OFFSET function or the function that uses the resultant reference. As
coded, it refers to a range that contains the top left corner of MyGrid
as one of its corners, and whose height and width are dependent on the
values in X and Y. The #VALUE may be returned by the function that
contains the OFFSET reference.

DOR

I just noticed that you said you were trying to pick members of that
grid. Maybe you should use X and Y as the second and third parameters,
e.g.

=OFFSET(MyGrid,X,Y,1,1)

which will refer to individual cells on row X+1 and Y+1 of the grid,
or use

=OFFSET(MyGrid,X-1,Y-1,1,1)

if you want the cell at (X,Y) rather than (X+1,Y+1).

HTH

Declan O'R

Thank you to both Ron and DOR. In the end I used


=OFFSET(MyGrid,X-1,Y-1,1,1) because it keeps the return value to a
single cell range.


Steve