#Value error when using the offset function

[helium]

I'm trying to get the offset function to display a 2 by 2 matrix from a table of numbers starting at A1 and moving 4 rows down and 6 columns across from the A1 reference point.

I've used the formula = OFFSET(A1,4,6,2,2)

But I get a #Value error.

What am I doing wrong?

The offset function.xlsm

[jeffreybrown]

What are you looking to do with it once you find it?

Are you looking to sum it?

Please Login or Register  to view this content.

The OFFSET function used in a worksheet cannot return the results of an array so you have to use a wrapper function and in this case, SUM.

You can also use the OFFSET function in a named range which would identify the range, but again, what is your goal?

[helium]

Hi Jeff,

I just want to return the matrix table unaltered.

[protonLeah]

Try selecting the four destination cells. Enter the formula and confirm with Control+shift+enter to commit the array(?)

[helium]

Thanks worked great.

[Cutter]

Please remember to mark your thread as SOLVED. Click Forum Rules @ top of page to see instructions in rule #9. Thanks.