Excel cell reference

[Esser_1845]

Hi there,

I would like to copy a cell reference to a cell that is 5 cells below. at the same time, however, the reference is changed by 5 cells downwards. For example: Cell A1 =C1. If you copy this =C1 into cell A6, it says =C6. But I would like to have =C2 inserted there.

Thank You.

[#2]

@Esser_1845 

In A1:

=INDEX(C:C,(ROW(A1)-1)/5+1)

Copy/paste to A6, A11, ...

[#3]

Thanks,
I put the comma after C:C through ; replaced. Otherwise it wouldn't have worked. But then I get the message #Name?.

[#4]

@Esser_1845 

Which language does your Excel use?

[#5]

German

[#6]

@Esser_1845 

In German, it is

=INDEX(C:C;(ZEILE(A1)-1)/5+1)

[#7]

Thanks very much. I should have written down my whole question best. In fact, I want to see the first entry in the AR column in cell B5. Then in cell N5 the second entry of the column AR. In cell Z5 the third entry of column AR. In cell B34 the fourth entry of the AR column and so on.

[#8 · Accepted solution]

@Esser_1845 

The row numbers of the cells with the formulas increase by 25 (rows 5, 34, ...) and the column numbers increase by 12 (B=column 2, N=column 14, Z=column 26). We have 3 entries per row, so the index number in column AR increases by 3 for each row.

In B5:

=INDEX($AR:$AR;3*(ZEILE(B5)-5)/29+(SPALTE(B5)-2)/12+1)

[#9]

Thank you very much. Now it's working.