Hi
=OFFSET($A1,row,column)
In all cases the column offset is 0 from columns A, as all values
required are in column A.
But, as we drag the formula across the page, we want to take each
successive row value down column A so the formula posted was
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
The $ before the A fixes it to be column A, the final 0 in the formula
ensuring that we do not offset by any columns from the starting column.
This formula is entered in B1 so COLUMN() = 2 and ROW() = 1
So the first offset from $A1 is (2-2)+(1-1)*5 which = 0 rows from A1 and
0 columns from A1 so it is the value in A1
As the column number goes up, we get (3-2)+(1-1)*5 which equals 1 then
2, then 3 reaching a value of 5 when you get to column G.
As the formula is then copied down to the next row, row 2, the formula
in cell B2 evaluates to an offset of
(2-2)+(2-1)*5 which equals 5, but now the offset is from cell $A2, as
the row number was left relative in the formula, and not absolute as the
column reference was made.
So it picks up the value that is 5 rows below A2, which is the value in
A7.
Hopefully, from the above, you can work out what you need the values to
be within the formula, if you are starting from a location other than
A1.
--
Regards
Roger Govier
"rollinn95z" <
[email protected]>
wrote in message