deleting a cell without affecting cells which refer to that cell

[caffeine_demon]

Hi,

Suppose I have 1 sheet with data on:

col1 col2
a b
b C
c d
d e
e f
f g
g h
h i
i j
j k
k l
l m
m n

and another sheet which refers to sheet1

(eg a1 = sheet1!a1, a2= sheet1!a2 b1 = sheet1!b1 and so on..)

if I delete a row from sheet 1, then sheet 2 displays #ref in that row
eg:
g h
#REF! #REF!
i j

I want it to show a reflection of sheet 1 - ie
g h
i j
j k

is there a way to do this?

 

[Jonmo1]

Ideally, you should try to NOT delete rows...
Perhaps instead you can just Clear Contents, then re-sort the data Ascending.
That will send blanks to the bottom..


But if you MUST...

Try this in A1 and filled Down/Right

=INDEX(Sheet1!$A$1:$B$13,ROW(),COLUMN())

 

[Andrew Poulsom]

In case you don't want your formulas to start in A1:

=INDEX(Sheet1!$A:$B,ROW($A1)-ROW($A$1)+1,COLUMN(A$1)-COLUMN($A$1)+1)