Return offset array

[mrblister]

I have a formula:

"OFFSET(INDIRECT($B$3),2,0)" array entered

B3 contains a range e.g. "c8:h8" without quotes

I want to copy the above formula down. The value in B3 is a singular global value, so I don't want to create a column for it. So I tried:

"OFFSET(INDIRECT($B$3),2+row(),0)" array entered, but it only returns the first value in the array.

Any idea how to fix this, or alternatives so I can copy the formula down?

 

[easy2understandexcel]

Ok, so it seems that when you are getting to the end of the predefined array (C8:H8) anywhere south of that range is outside of it, you are having an error.
If you make the starting point a single point instead of an array it should work: Start here... got x up or down, go y up or down... then the 3rd and 4th argument of offset indicate the size of the range.

I assume you want it to cover the range: C8:H8, C9:H9, C10:H10... as you go down...

so it would be --> OFFSET(INDIRECT($B$3),2+row(),0,1,6)

To be honest, I would love it if one of the veterans here explained exactly why... but that will make it work for you.

 

[FDibbins]

I agree that you should start with a fixed (single) cell, then create the INDIRECT range from that. Something like...
INDIRECT($A$2, 1,1,0,6)
Not sure you need the offset for this.

Ignoring the fancy formulas, what would the range look like as you copied it down?

 

[Tetra201]

Try this: OFFSET(INDIRECT($B$3),ROWS($A$1:$A2),0)

 

[Aladin Akyurek]

OFFSET and INDIRECT are volatile and to some degree (functionally) kindred. Better avoid mixing them altogether...

Let K1 house the first instance of the required formula.

=OFFSET($C$8,ROWS($K$1:K1)-1,0,1,COLUMNS(C8:H8))

This picks out everything from C8:H8 (Note that you can replace the COLUMNS bit with 6 if so desired.).

Copied down to K2, the formula will pick out C9:H9, and so on.

 

[easy2understandexcel]

I agree that you should start with a fixed (single) cell, then create the INDIRECT range from that. Something like...
INDIRECT($A$2, 1,1,0,6)
Not sure you need the offset for this.

Ignoring the fancy formulas, what would the range look like as you copied it down?

FDibbz, are you sure you can add offset parameters like that without an offset function?

 

[FDibbins]

FDibbz, are you sure you can add offset parameters like that without an offset function?

Must have been having a senior moment, you are correct. That should all have been OFFSET
OFFSET($A$2, 1,1,0,6)

 

[mrblister]

Thanks to all those that replied!

I tried out all your posted solutions.

so it would be --> OFFSET(INDIRECT($B$3),2+row(),0,1,6)

Thanks for your post! Unfortunately, this seems to yield the same issue, in that it only returns the first value of the array.

OFFSET($A$2, 1,1,0,6)

Thanks for your post! Unfortunately, it gives me a #REF error.

Try this: OFFSET(INDIRECT($B$3),ROWS($A$1:$A2),0)

Thanks for your post! It seems to work (quick test), though I haven't implemented it yet in my formula. I'm assuming it's because ROWS is less "volatile" than ROW?

=OFFSET($C$8,ROWS($K$1:K1)-1,0,1,COLUMNS(C8:H8))

Thanks Aladin! Not sure why, but when I copy the formula down, it returns every second row. I may have implemented it wrong though.

 

[Aladin Akyurek]

[...]
Thanks Aladin! Not sure why, but when I copy the formula down, it returns every second row. I may have implemented it wrong though.

It does work as advertised... See: https://dl.dropboxusercontent.com/u/65698317/mrblister Return offset array .xlsx

In fact, you need to feed this offset formula to some, say, aggregation function... Entered in a cell, it will give #VALUE!. Select the cell and hit F9. That will show you what it really is.

 

[FDibbins]

Ignoring the fancy formulas, what would the range look like as you copied it down?

Perhaps you missed this part in my post above?