Formula problem

[22strider]

Hello Friends

I have prepared a template, in one of the sheets (say sheet1) in template there are some formulae that are referencing some cells in other sheet (say sheet2) in the same file.
I am facing this strange problem that when records are removed from the source sheet (sheet2), the formula in the sheet1 changes. Following is exactly what is happening:

The formula in Sheet1 is ='All-HPN-With-Tools'!I2 (so the cell in Sheet1 is referencing value in cell 'I2' in the sheet 'All-HPN-With-Tools')
The problem is that everytime I delete row 2 in the sheet 'All-HPN-With-Tools', the formula in Sheet1 becomes
='All-HPN-With-Tools'!#REF!

I cannot figure out why I2 in the formula gets replaced by #REF! ?

I hope someone will be able to help me out here.

Thanks for your time
Rajesh

 

[Vidar]

Hi Rajesh
When you delete the row in sheet 'All-HPN-With-Tools', you delete the cell reference.
Try this:
=INDIRECT("'All-HPN-With-Tools'!I2")

Vidar

 

[Aladin Akyurek]

Hello Friends

I have prepared a template, in one of the sheets (say sheet1) in template there are some formulae that are referencing some cells in other sheet (say sheet2) in the same file.
I am facing this strange problem that when records are removed from the source sheet (sheet2), the formula in the sheet1 changes. Following is exactly what is happening:

The formula in Sheet1 is ='All-HPN-With-Tools'!I2 (so the cell in Sheet1 is referencing value in cell 'I2' in the sheet 'All-HPN-With-Tools')
The problem is that everytime I delete row 2 in the sheet 'All-HPN-With-Tools', the formula in Sheet1 becomes
='All-HPN-With-Tools'!#REF!

I cannot figure out why I2 in the formula gets replaced by #REF! ?

I hope someone will be able to help me out here.

Thanks for your time
Rajesh

You get a #REF! error because the deletion renders the reference in:

='All-HPN-With-Tools'!I2

invalid.

If you want to fix the reference to I2 at all times, try something like...

Either:

=INDIRECT("'All-HPN-With-Tools'!I2")

Or:

=INDEX('All-HPN-With-Tools'!I:I,2)

 

[22strider]

I tried INDIRECT after your suggestion; it appears that the formula with INDIRECT function does not refere to next cell when copied. I have VBA code that copies the row with formula and should refer to the next row(s) when copied down. Any suggestion?

 

[22strider]

I am having same problem using INDEX as well. Can't copy; the copied formula keep referencing to fixed cell address.

 

[Aladin Akyurek]

I tried INDIRECT after your suggestion; it appears that the formula with INDIRECT function does not refere to next cell when copied. I have VBA code that copies the row with formula and should refer to the next row(s) when copied down. Any suggestion?

Either:

=INDIRECT("'All-HPN-With-Tools'!"&CELL("address",I2))

Or:

Let A2 house the first instance of the formula:

=INDEX('All-HPN-With-Tools'!I:I,ROWS($A$2:A2)+1)

However, why do you delete rows in the input sheet?

 

[Vidar]

Or:
=INDIRECT("Sheet1!I"&2+ROWS($A$2:A2)-1)

 

[22strider]

I am deleting the rows because the Template will be shared by a large group and they will be replacing input sheet(s). In that process they may simply replace data with new or delete the current data and then paste new input data.

 

[22strider]

Thanks Aladin and Vidar for your help;

=INDIRECT("'All-HPN-With-Tools'!"&CELL("address",I2)) does what I need to do.

Thanks again
Rajesh