How Do I Avoid #REF! In Formulas?
 

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?
--
tb

How Do I Avoid #REF! In Formulas?
 

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)

How Do I Avoid #REF! In Formulas?
 

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

Hi tb

May be the INDIRECT & ADDRESS functions:

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

How Do I Avoid #REF! In Formulas?
 

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.

How Do I Avoid #REF! In Formulas?
 

On 2/7/2013 at 2:07:57 AM Kevin@Radstock wrote:


Hi tb

May be the INDIRECT & ADDRESS functions:

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


tb;1609161 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?
--
tb

It seems to be working. Thanks, Kevin.

--
tb