Good morning all,

Sheet 1 has some simple table, no formulas.

Sheet 2 has a column that duplicates Col A from back in Sheet 1. Simple.


But, due to some entries and sorting and such I delete a row now and then on Sheet 1.

Those cells in Sheet 2 become #REF!

So, what once was Sheet2!a5 (which literally used to say ='Sheet1!a5'), says #REF! if I delete that row over in Sheet 1.


I'm not clear why. There still exists an A5--everyone just slid up a row.

Is there a way to lock it down to disregard a shift in rows and just return the value of whoever happens to be sitting at address A5?