SOLVED

# Excel cell reference

Occasional Contributor

# Excel cell reference

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.

8 Replies

# Re: Excel cell reference

In A1:

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

Copy/paste to A6, A11, ...

# Re: Excel cell reference

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

# Re: Excel cell reference

Which language does your Excel use?

German

# Re: Excel cell reference

In German, it is

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

# Re: Excel cell reference

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.
best response confirmed by Esser_1845 (Occasional Contributor)
Solution

# Re: Excel cell reference

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)

# Re: Excel cell reference

Thank you very much. Now it's working.