Solution Required to prevent #Ref! when rows deleted

[Andibevan]

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

 

[Aladin Akyurek]

1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

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.

 

[Andibevan]

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

1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

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.

 

[Aladin Akyurek]

INDIRECT affects performance/speed.

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

1)

=INDIRECT("Sheet1!A1")

2)

=INDEX(Sheet1!A:A,1)

The 2nd is less expensive.

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.