How Do I Avoid #REF! In Formulas?

[tb]

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?

 

[CellShocked]

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)

 

[GS]

I strongly recommend using local scope when adding names, so name
conflicts don't occur when copying/moving the sheet to another
workbook. You can force local scope by prefixing the range name with
the sheetname as follows:

'<sheetname>'!RangeName

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[plinius]

Il 07/02/2013 02:06, tb ha scritto:

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?

Modify
=Sheet1!A15
in
=INDIRECT("Sheet1!A"&ROW(A15))

Hi,
E.

 

[tb]

Hi tb

May be the INDIRECT & ADDRESS functions:

=INDIRECT(ADDRESS(ROW(A15),1,4,1,"Sheet1"))

It seems to be working. Thanks, Kevin.