Making a formula unchangable

[ener]

Hi Guys,

I have a formula that looks like this: IF(Sheet1!M20<>"",Sheet1!M20,"") and I want that formula to absolutely fixed and nothing can change. My problem is that if I have an incorrect value (reference) in Sheet 1 this formula will change to IF(Sheet1!#REF!<>"",Sheet1!!#REF!,"") and the only way to get it back to the original formula (IF(Sheet1!M20<>"",Sheet1!M20,"")) is by changing it back manually or dragging the formula above to automatically fill it in. However doing this all the time is pretty annoying. Any way to absolutely 'fix' a formula so it cant change at all? Tried protecting/locking it but didn't work.

Hopefully someone can help

Thanks

 

[Jubjab]

Try

=IF(INDIRECT("Sheet1!M20")<>"",INDIRECT("Sheet1!M20"),"")

 

[Andrew Poulsom]

Another way:

=IF(INDEX(Sheet1!M:M,20)<>"",INDEX(Sheet1!M:M,20),"")