I'll try. It's a bit difficult to put into words, at least in short words.
We'll use the first formula I put up as an example:
=OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0)
OFFSET takes 3 parameters (actually there are 2 more, but we don't need them)
#1 - the reference address, so we tell it we want to start at Sheet1!$L$3
(and the $ symbols tells Excel not to change that reference point as we fill
the formula to other columns or rows).
#2 - a number of rows to offset from the reference an offset of 1 would
point to the row below the reference, a value of 0 = same row, and negative
values refer to rows above the reference point, and
#3 - number of columns to offset: positive = columns to the right, zero =
same column, and negative numbers = columns to the left of the reference. We
always use zero in our formula because we want to remain in the same column.
The real trick is figuring out a row offset that accounts for 2 things:
#1 - the fact that this particular formula is only going to appear on every
other row
#2 - the other fact that your groups are 8 elements long.
Two other things you need to know: ROW() and COLUMN() when used like they
are without anything in the () returns the row/column number that they are
in, where ever that happens to be.
So look at the Row parameter of the formula:
(COLUMN()-3)+(((ROW()-7)/2)*8)
Since we're transposing things, we use the current column number our formula
is in and subtract some value to get a zero in that first formula. Since
you're putting this into cell C7 and column C is column #3, then 3-3 = 0.
When you fill this formula over into column D that evaluates to 4-3 = 1.
That pretty much takes care of the immediate relative position in a group,
but we have to take into account where the group starts at in your original
list, since a new group starts every 8 rows.
That's where the (((ROW()-7/2)*8) comes in
Remember this formula is in C7, and so ROW() = 7 in the first formula, and
7-7 = 0 and 0/2 is still 0 and 0*8 is again, still zero. So for first
formula, it says NO row offset.
Take both halves and add them together: in C7 it is 0+0=0, but in D7 it is
1+0 = 1 (which is same as OFFSET(L3,1,0) or same as referencing L4). This
continues across to column J where COLUMN()-3 becomes 10-3=7 but second half
is still 0, so it evaluates as OFFSET(L3,7,0) which is same as referencing
L10.
When the TWO formulas are filled down the sheet, then the $L$3 formula gets
put into row 9. Let's look at how it computes at C9:
COLUMN()-3 is still 3-3=0, but
(((ROW()-7)/2)*8) becomes (((9-7)/2)*8 or ((2)/2)*8 or ((1))*8 = 8
which is same as writing OFFSET(L3,8,0) and that gives us an address of L11
- the start of your next group!
So you see that the 3 in COLUMN()-3 came from the fact that your formula was
palced into column #3 (column C). The 7 came from your choice of C7 as the
starting point, the divide by 2 was caused by there being two formulas in the
sequence, so the value of ROW()-7 is going to increase as 2, 4, 6, 8, etc,
but we have to cut that in half to get an offset back into the original table
without skipping rows in it. Finally, the 8 came from the size of the group.
I hope that's a lucid explanation.
