Array Offset Function
 

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

Array Offset Function
 

Try something like this:

=OFFSET('Loop 4'!AM$17,0,ROWS($17:17)*3)

Copy that formula down as many cells as you need.

CAVEAT: Check how many column you'll need. XL2002 only has 256. Your
comment about 1000 more values would exceed that limit.

Does that help?

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

XL2002, WinXP-Pro


"maperalia" wrote:

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

Array Offset Function
 

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

Best regards.
Maperalia

"Ron Coderre" wrote:

Try something like this:

=OFFSET('Loop 4'!AM$17,0,ROWS($17:17)*3)

Copy that formula down as many cells as you need.

CAVEAT: Check how many column you'll need. XL2002 only has 256. Your
comment about 1000 more values would exceed that limit.

Does that help?

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

XL2002, WinXP-Pro


"maperalia" wrote:

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