Excel reference to always the same cell

[Talicius]

Hi, I have sheet1 and sheet2.

Let say I have some data in sheet1, in cell A1, A2, A3... for example. And I want to reference cell A1 in sheet2, then A2, then A3.

But the thing is if you e.g. detele the cell from sheet1, or you add some rows or columns the reference changes accordingly (to A2, or B1 or whatever...).

I want to reference cell A1 no matter what happens with the actual cell, always reference to A1.

Do you have some function for that?

Thank you very much!

 

[RoryA]

That is pointing at row 1, not row 8. ROWS returns the number of rows in a range, and that is a one row range. If you want row 8, use: ROWS($A$1:$A8) and note that this part of the formula should not point at the other sheet.

 

[Talicius]

This works perfectly: =INDEX('PHM-rozuctovanie pravidelne'!U:U; 8)
I just can't autopopulate the formula.

 

[Talicius]

This might be it:
=INDEX('PHM-rozuctovanie pravidelne'!U:U;ROWS('PHM-rozuctovanie pravidelne'!$U$1:$U8))

 

[RoryA]

As I just said:

=INDEX('PHM-rozuctovanie pravidelne'!U:U; ROWS($A$1:$A8))

You do not want the ROWS part to refer to the other sheet where you may be deleting rows.

 

[Talicius]

As I just said:

=INDEX('PHM-rozuctovanie pravidelne'!U:U; ROWS($A$1:$A8))

You do not want the ROWS part to refer to the other sheet where you may be deleting rows.

Sorry I thought it didn't work. But now it works it was the $A$1:$A8 and not $A$8:$A8 that made the error.
Seems like this should work.