How to make a repeating pattern in MS Excel with running alphabet letters and numbers-CodePudding

I've been trying to search the web for an answer with no avail so I decided to ask this here. I have a matrix in MS Excel and I would like to find a repeating pattern on the last row of the matrix such that both the column letter and the row number are running in the pattern.

I tried creating this pattern in the usual way by making few examples in the excel and then dragging the -sign over the cells. However, only the alphabetical letter runs in the pattern but the numbers do not.

I have below a minimal working example. I have a 4 x 4 matrix and I would like the last row to contain all the diagonal elements of the matrix, that is the numbers 1, 6, 11 and 16. But when I try to repeat the pattern after manually plugging into the first three cells of the blue row as:

=A1, =C2, =B3

Then when I try to use the -sign and repeat the pattern only the letter is incremented and I get the value in cell D1 instead. How to create a pattern which reads the diagonal elements of the matrix? Thank you!

UPDATE: Thank you everyone for your help. One addition to previous, many of the given formulas (for example the INDEX-approach) seem to work when my matrix is in the top-left corner of the Excel-file. I noticed that if I add rows or columns to the excel (above and on the left side of the matrix) the formula produces different values, why is this?

Here you see the INDEX-approach prior to adding any columns to excel:

And here you see the same matrix and formulas after adding one column on the left side of the matrix:

CodePudding user response：

You can use =INDEX($A$1:$D$4,COLUMN(A1),COLUMN(A1))

Broken down: column() indicates the current column, e.g. in column A it's 1, for B it's 2 etc

INDEX looks up the indicated row/column combination in the array

edit1: replaced indirect with index edit2: changed column() to column(A1) as suggested by @JvdV

CodePudding user response：

I created the yellow line with just 1 to 4 incremented.

Then in the blue cells, I have the formula as seen on screen :

As the point is just to get RiCi incremented from 1 to n, the formula in the blue cells becomes:

=INDIRECT("R"&A5&"C"&A5; FALSE)

That you can also drag on the left for autocompletion.

CodePudding user response：

You can use the OFFSET function, to target a specific row/column using a base cell reference and an offset by a number of rows and columns. In your example here, in B5 you want to offset by 1 row and 1 column from A1 and so on...

So you could use the following formula in cell A5 and then copy across the row: =OFFSET($A$1,COLUMN()-1,COLUMN()-1)

Edited: since I realised from @CIAndrews solution that A5:=COLUMN(A5) gives the same value as A5:=COLUMN(), and the latter is neater.