Array Offset Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maperalia - 25 Jan 2006 17:47 GMT

I have the following formula that offset values from the "Loop 4" sheet. This
formula allows me to offset values every three columns from the same row
(17). Therefore, this is what I have typed in my cells to call this data:
=OFFSET('Loop 4'!AM$17,0,3)
=OFFSET('Loop 4'!AM$17,0,6)
=OFFSET('Loop 4'!AM$17,0,9)
=OFFSET('Loop 4'!AM$17,0,12)
=OFFSET('Loop 4'!AM$17,0,15)
.........
.........
.........
.........
=OFFSET('Loop 4'!AM$17,0,60)

However, I was given 1000 more values under the "Loop 4" sheet. I have
started adding these functions manually in each cells because I could not
array the function to change the column number (every three value) only.
I wonder if anybody knows the way to do it array this function so I do not
have to do manually.

Thanks in advance.
Maperalia

Reply to this Message

somethinglikeant - 25 Jan 2006 18:22 GMT

You should play around with the functions

=ROW()
=COLUMN()
using a factor of 3 were appropriate in our OFFSET Function
This may give you what you need.

somethinglikeant

Reply to this Message

Tom Ogilvy - 25 Jan 2006 18:23 GMT

=OFFSET('Loop 4'!AM$17,0,row(A1)*3)

then drag fill down

Signature

Regards,
Tom Ogilvy

> I have the following formula that offset values from the "Loop 4" sheet. This
> formula allows me to offset values every three columns from the same row
[quoted text clipped - 18 lines]
> Thanks in advance.
> Maperalia

Reply to this Message

maperalia - 25 Jan 2006 19:35 GMT

Tom;
Thanks you very much IT IS WORKING PERFECTLY!!!!

Best regards
Maperalia

> =OFFSET('Loop 4'!AM$17,0,row(A1)*3)
>
[quoted text clipped - 23 lines]
> > Thanks in advance.
> > Maperalia

Reply to this Message