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!

 

[diddi]

this is a handy one that has a lot of uses. the $ sign locks the part of the cell name it is in front of,
so =A$1 would lock the row to row 1 but allow the column to change
=$A1 locks the column
or = $A$1 locks the cell so it never changes

 

[Talicius]

No, that actually doesn't work!
Try to delete the cell and you get #REF error.
Delete the cell and all the other cell change their reffeence.

 

[Talicius]

The reference must always be e.g. A1 no matter what happens with A1, no matter where you A1 move.

 

[diddi]

indeed. you cant point a heap of cells to something and then delete it. that is illogical
if you meant to lock the cell and protect the worksheet, then that would work

 

[Talicius]

I need to work with sheet1 as my data sheet - add and delete rows. But as you do this, you change the reference in sheet2, that musn't happen.
And I need sheet2 to always reference A1,A2,A3 etc... no matter what happens with the original cells, their location or values.
There is function INDIRECT but it doesn't work very well for me.

 

[RoryA]

You could use:

=INDIRECT("Sheet1!A1")

but that is a volatile function, which will slow your workbook down if you use it a lot, and the fact that you appear to be mirroring one sheet to another sounds like bad design to me.

 

[RoryA]

You could also use:

=INDEX(Sheet1!A:A,1)

as long as you won't delete column A on sheet 1.

 

[Talicius]

Index might be what I'm looking for I will test it.

 

[Talicius]

That seems like could work, but when I want to populate my sheet with this function and I start with:

=INDEX(Sheet1!A:A,1) the next reference is also =INDEX(Sheet1!A:A,1) and the next =INDEX(Sheet1!A:A,1).
How I make it =INDEX(Sheet1!A:A,1), then =INDEX(Sheet1!A:A,2), then =INDEX(Sheet1!A:A,3) etc...