Solution Required to prevent #Ref! when rows deleted
 

Hi All,

I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have
a template that uses simple =Sheet1!A1 formulas.

The problem I have is that if I delete row A on sheet 1, the formula on
sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF!

How do I change this so that I can happily delete rows from Sheet 1 without
messing up my template.

I have tried making the formulas absolute and this doesn't work?

Any suggestions??????

Ta

Andi

1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

Andibevan wrote:
Hi All,

I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I have
a template that uses simple =Sheet1!A1 formulas.

The problem I have is that if I delete row A on sheet 1, the formula on
sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF!

How do I change this so that I can happily delete rows from Sheet 1 without
messing up my template.

I have tried making the formulas absolute and this doesn't work?

Any suggestions??????

Ta

Andi



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Thanks - what do you mean by less expensive? Are you referring to system
resources?


"Aladin Akyurek" wrote in message
...
1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

Andibevan wrote:
Hi All,

I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I
have
a template that uses simple =Sheet1!A1 formulas.

The problem I have is that if I delete row A on sheet 1, the formula on
sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF!

How do I change this so that I can happily delete rows from Sheet 1
without
messing up my template.

I have tried making the formulas absolute and this doesn't work?

Any suggestions??????

Ta

Andi



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

INDIRECT affects performance/speed.

Andibevan wrote:
Thanks - what do you mean by less expensive? Are you referring to system
resources?


"Aladin Akyurek" wrote in message
...

1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

Andibevan wrote:

Hi All,

I have a spreadsheet where on sheet 1 I have some data and in sheet 2 I

have

a template that uses simple =Sheet1!A1 formulas.

The problem I have is that if I delete row A on sheet 1, the formula on
sheet 2 that used to say =Sheet1!A1 turns to =Sheet1!'REF!

How do I change this so that I can happily delete rows from Sheet 1

without

messing up my template.

I have tried making the formulas absolute and this doesn't work?

Any suggestions??????

Ta

Andi



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.