Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Index Match Offset function | Excel Worksheet Functions | |||
Code to determine if a cell contains an array function? | Excel Discussion (Misc queries) | |||
Troubles with the OFFSET function | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
OFFSET and array formulae | Excel Discussion (Misc queries) |