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]

You could use:

=INDEX(Sheet1!A:A,ROWS($A$1:$A1))

If the formula is in column A, then use a different column for the reference in the ROWS function.

 

[Talicius]

You could use:

=INDEX(Sheet1!A:A,ROWS($A$1:$A1))

If the formula is in column A, then use a different column for the reference in the ROWS function.

When I type it like this it doesnt work. This ROWS($A$1:$A1) references the sheet2 and not sheet1.

 

[RoryA]

Yes, that is as it should be. It's only there to return the 1, 2, 3 etc as you copy the formula down. So what exactly do you mean by "doesn't work"?

 

[Talicius]

Yes, that is as it should be. It's only there to return the 1, 2, 3 etc as you copy the formula down. So what exactly do you mean by "doesn't work"?

It returns 0 as value.

 

[RoryA]

And what is in A1 on Sheet1? Nothing?

 

[Talicius]

And what is in A1 on Sheet1? Nothing?

No, there is value.

 

[RoryA]

Then the most likely option is that you created a circular reference; what cell is that formula in?

 

[Talicius]

The cell with the INDEX function is in another sheet, can you upload here Excel file how it should look, please? Thank you very much.

 

[RoryA]

It is not possible to upload files here.

Which cell is the formula in, and what is the exact formula?

 

[Talicius]

=INDEX('PHM-rozuctovanie pravidelne'!U:U;ROWS('PHM-rozuctovanie pravidelne'!$U$8:$U8))
This gives "0" even tho I have value in sheet 'PHM-rozuctovanie pravidelne' and cell U8.