Keeping a cell reference on another sheet constant even with moving

[kapadons]

Hello all,


Trying to keep a =cell to always refer to that cell number (not the contents but the actual cell itself) even if i drag/move the contents of referenced cell. Absolute reference ($a$1 doesnt work)


For clarity;

I want Sheet2 A2 to ALWAYS reference to sheet1 A5.Even if I drag the contents of sheet1a5 or add a new row. A simple "=sheet1!a5" references it but if I drag the contents to say a6 it changes the formula to "=sheet1!a6". The exact same thing happens even with $$ added. I would think a form of "indirect" would be the trick but If I use =indirect(sheet1!A5) I get a reference error.

Any help would be greatly appreciated

 

[austinandrei]

You should use quotation marks.
=indirect("Sheet1!A5")

 

[kapadons]

Think I found the issue. For some reason you can't have a space in the name of the sheet. Had to put an underscore in there.

 

[austinandrei]

You actually can. But you need to put ' in the name of the sheet so there would be no error. But if you're already okay with underscore then its all okay.
Anyway, here's a way
=INDIRECT("'Sheet 1'!A5")