On Thu, 7 Feb 2013 01:06:13 +0000 (UTC), "tb"
wrote:

I am using Microsoft Excel 2007.

My spreadsheet has two tabs, Sheet1 and Sheet2.

In Sheet2 I have the following formula going down Col. A linking to
data on Sheet1:

='Sheet1'!A15
='Sheet1'!A16
etc. etc.

But if I delete some rows on Sheet1, then the formula on Sheet2 becomes:

='Sheet1'!#REF!

What do I need to do so that the dreaded #REF! does not happen when I
delete rown on Sheet1?


used named ranges

In the upper left, where the cell name is declared.

When you hover your cursor there, note that it shows up as an excel
object called "name box". When you put your cursor there and give your
data specific cell location an actual name, you can then refer to said
name in your formula, instead of a hard cell location.

Then, no matter what you do as far as rows and such, that cell location
will always carry that assigned "range name", and that formula will
always point there.
It is a bit of data entry labor and cursor moves to do each cell
involved, but done right, and you can still drag and carry your formula.

(end your named range in a numeric figure the number of digits long you
intend fill out)