I don't know if you wish an explanation of the Index() function or the
incrementing procedure, so here's both.
The Index() function has a couple of forms, array and reference, and
differing uses within each form.
To simplify, here, we're using the array form, which will return the
contents
of a cell or array of cells.
To help you understand, on a new sheet, create an array of numbers, from say
C18 to say E22.
Say you enter the numbers across and down,
1, 2, 3
4, 5, 6
7, 8, 9
.... etc.
To return a number from that array using Index(), you first enter the array
of cells you wish to reference:
=Index(C18:E22
Then you enter the row number,
=Index(C18:E22,2
Then you enter the column number,
=Index(C18:E22,2,2)
And you'll get a return of 5.
You'll notice that the row and column numbers are *relative* to the array.
That means the second row of C18 to E22, which is
C19 to E19,
And the second column of C18 to E22, which is
D18 to D22.
Therefore, the intersection of the 2 references is D19, which contains the
value 5.
So,
=Index(C18:E22,1,1)
Starts ar Row1 - Column 1 *OF THE INDEXED ARRAY*
which is C18, so it returns 1.
While
=Index(C18:E22,5,3)
returns 15
And
=Index(C18:E22,6,3)
returns a #REF! error, since the references are *not within* (outside) the
indexed array.
Now, we've been using a 2 dimensional array (C18 to E22), so we've needed 2
references,
row & column.
If we change the array to a single dimension, row *or* column, we'll need
only a single reference.
=Index(C18:C22,3)
returns 7
While
=INDEX(C21:E21,3)
returns 12
So far we've referenced an array of a set number (block) of cells, C18:E22.
We can also reference an *entire* row or column.
=Index(A:A,3)
=Index(18:18,5)
BUT remember, the referenced location number is *relative*.
That means when indexing an entire column,
you're always starting at Row1,
and when indexing an entire row,
you're always starting at Column A.
Now that you have a notion of how this form of Index works,
the idea of being able to reference a multitude of cells,
*without* having to manually enter a large number of formulas,
is to *copy* the formula.
BUT ... we still have to change those references numbers,
since copying will only revise the cell references of the array.
There are a number of different functions that will return a number,
dependent on their location, or how/where they are copied.
Not going into all of those here, we'll just look at one that's rather
robust.
On your test sheet, in say G18, enter:
=Rows(1:1)
In H18, enter:
=Rows($1:1)
In I18, enter:
=Rows(1:10)
And in J18, enter:
=Rows(1:$10)
Now, select *all 4 cells*, and drag down to copy for 5 or 6 rows.
You can see what each formula returned after copying down.
Click in the individual cells, and look in the formula bar to see how each
formula was changed by the copying.
Exactly the same thing happens when we wish to copy across, where we'll
need to use the Columns() function.
=Columns(A:A)
=Columns($A:A)
=Columns(A:Z)
=Columns(A:$Z)
Drag these across and see what they return.
There is no simple way to describe how to make those reference numbers
increment in different ways to match different requirements.
That takes math and common sense.
Now, to your scenario.
We indexed the entire row (18:18), meaning we're starting at Column A
(column 1).
=Columns($C:C)
returns a "1".
We could just as well have used $A:A or $Z:Z, since they both return a "1",
but since we wanted to start at Column C, I used that reference.
So,
=INDEX(18:18,COLUMNS($C:C))
returns the contents of cell A18.
Since we're looking for the contents of the 3rd column, we'll start with:
INDEX(18:18,3*COLUMNS($C:C))
which equates to 3*1
which equates to
=INDEX(18:18,3)
Copying across to the next column gives us
Columns($C
)
which yields a 2
so 3*Columns($C
)
equates to 6
which equates to
=INDEX(18:18,6)
And you can now see how the progression evolves to columns 3(C), 6(F), 9(I),
12(L), ...etc.
Carrying this a step further, we could have indexed a set range instead of
the entire Row 18.
Say C18:AA18
With that range indexed, we must start at relative position 1, since C18
starts the range.
In that case, following what we have learned here, we would use a formula
such as this:
=INDEX($C18:$AA18,3*COLUMNS($C:C)-2)
OR
=INDEX($C18:$AA18,3*COLUMNS($A:A)-2)
Which *exactly* duplicates the returns of the original suggested formula:
INDEX(18:18,3*COLUMNS($C:C))
But of course will *not* return any values beyond AA18.
Also take note:
There is a Row and a Column function (without the "s").
These are different from what we're talking about here.
