How do I link cells, sheet to sheet, to recognize row deletions?

Oh and also to make it so that blank cells in the source are also blank in
the destination (instead of a 0) Thanks!

"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.