Newbie Q - Preventing formulas to be updated after an insert

A

Atchoum

Hi,

I am wondering if there is a way to prevent a formula to be updated after an
insert.
As an example, let's say that I have a cell on Sheet2 that references
Sheet1!A4.
Let's say that I insert a row in Sheet1 above row 4. Automatically my
formula on Sheet2 is updated so that is references Sheet1!A5. Is there a way
to prevent this?

TIA.
 
M

Max

One way is to use INDIRECT()
which would always point to the target cells
irrespective of row / column insertions

In Sheet2
-----------
Instead of say in A1: =Sheet1!A4
try the functional equivalent in A1:
=INDIRECT("Sheet1!"&ADDRESS(ROW(A4),COLUMN(A1)))

The formula could then be copied across or down in the normal way,
which would increment the cell references relatively
as if you were extending normal link formulas
[ Example: =Sheet1!A4 copied across one cell gives =Sheet1!B4,
copied down one cell gives: =Sheet1!A5 ]
 
Top