Can you delete a row but keep a cell reference to it in a different sheet?

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?

it will be due to "hard" coded reference.. the solution is to change the reference so that it is not "hard" and is more flexible (ie. remove the $ from cell/column reference).

ok, let me go back and toy with those. much thanks. I'll be right back.

ok, that worked well. Thank you! I had to make an index column with some integers because dragging down didn't increment the "2" in the example. But, I didn't ask about that too, so...on me.

works fine now.

many thanks.