Fix a formula when relating Sheets

[ezahrai]

Hi,

I am relating two sheets; in sheet2 cell A1 I enter the formula
=Sheet1!A1. In sheet1 say cell A1 I enter a number which automatically
displays the number in cell A1 in sheet2.
Now if I move the cell A1 in sheet1 say to A5, the formula in sheet2
changes to Sheet1!A5.
Is there a way to keep the formula unchanged in sheet2 and keep it
always referening to A1. I would like to keep the formula in sheet2
unchanged when moving cells in sheet1.
I tried fixed referencing and cell protection but with no success. Any
help would be apprecited.
Thanks

 

[Anne Troy]

It's called an "absolute reference". Just change your formula to
=Sheet1!$A$1 BEFORE you copy it.
$A "holds" the column letter. $1 "holds" the row number. So if you only
wanted to hold the column letter, you would put =Sheet1!$A1
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[ezahrai]

Hi Anne,

Thanks for your response. I did try absolute referencing but it did not
work.

erfan

 

[Roger Govier]

Hi ezahrai

Try
=INDIRECT("Sheet1!$A$1")

 

[ezahrai]

Thanks. It worked! Much appreciated.

Erfa