You're welcome, and thank you for the feed-back.
As to explaining the workings of the formula:
Index() is a function that has 2 forms.
It can either return a value or it can return a reference.
In this case we're using it to return values.
Put simply, you can index a range, and then refer to a cell in that range.
Your starting point is C7, and I arbitrarily chose the last Column (IV) as
the ending column at Row1000.
The first argument in Index() is the range, the 2nd is the row, and the 3rd
is the Column.
So, =Index(C7:IV1000,1,1) would return the contents of C7, since
*everything* starts at the *start* of the indicated range.
Index(C7:IV1000,2,1) would return the contents of C8, while
Index(C7:IV1000,1,2) would return the contents of D7.
What we therefore need to do, is figure a way to increment the 2nd and 3rd
arguments, so that the rows and columns will change in the appropriate
series that we need, as we copy the formula.
The 2 best functions for this are Columns() and Rows().
In any cell, try:
=Columns(A:A)
Copy across a few columns and down a few rows.
You'll see that the return is "1", and it doesn't change with the copying.
Change it to:
=Columns($A:A)
And do the same, and you'll see that it increments when copying across, but
*does not* increment as you copy down.
This is because you anchored the function at "A" ( 1 ), while allowing the
second column reference to change.
Do the same test with the Rows() function,
=Rows(1:1)
and you'll see that the same thing happens, except it'll increment when
copied down and not when copied across.
So you can see how this would be useful in a formula that you want to copy
in 2 directions, with different references changing dependent on the
direction of the copy.
Therefore, you should now understand how:
=Index(C7:IV1000,Columns($A:A),Rows($1:1))
Would return the contents of C7.
Don't be confused ... as stated above ... the 2nd Index() argument
determines *ROWS*,
We're using the Columns() function in the 2nd argument because we want the
ROWS to increment as we copy *across*,
And the 3rd argument (Columns), to increment as we copy down.
The rest of the formula is simply a mathematical calculation to enable the
row and column references to follow the numerical series we need as they are
copied down and across.
Take:
=5*Columns($A:A)-4
And copy across and down, and take note of the returns.
Do the same with:
=3*ROWS($1:1)-2
After you understand the workings of Index(), the difficulty in completing
the formula is simply figuring out the mathematics to attain the numerical
sequence you're looking for.
