3-d reference is not updating

I copied a range of cells from Worksheet2 and went to Worksheet1 where I used
Paste Special, then clicked on Paste Link. The cell formulas now show a cell
reference back to Worksheet2. If I edit data in Worksheet2, it correctly
updates on Worksheet1. So far, so good.

However, if I delete a row in Worksheet2, that change does not show up in
Worksheet1. I get a #REF! error. If I add a row in Worksheet2, the added
row does not show up in Worksheet1, but all the cell references change row
numbers to reflect the new row number after the row was added.

What I really want to accomplish is that Worksheet1 is a single worksheet
with all the data from Worksheet2, followed by all the data from Worsheet3,
Worksheet4, etc so that Worksheet1 is always updated with any changes made on
the separate worksheets.

Am I expecting too much, or is it possible to do what I want to do?

You're expecting too much.

If it's at all possible, keep all your data in one worksheet (in a single
workbook). Then you can use filters and sorts to see the data that's important
at that time.

If you have to use different worksheets in the same workbook for
display/printing purposes, you can:

Select the range on Sheet1
Edit|copy (xl2003 menus)

Then select the new worksheet
Shift Edit|Paste picture link

(and continue to stack these pictures from different worksheets.)

Now any changes you make to the real ranges will be reflected in those pictures.

Make sure you include enough range to include any rows you insert--or you'll be
building those pictures again.

And since the new sheet is a bunch of pictures, you won't be able to make any
changes to the data displayed on in the pictures directly.

TigerMO wrote:

I copied a range of cells from Worksheet2 and went to Worksheet1 where I used
Paste Special, then clicked on Paste Link. The cell formulas now show a cell
reference back to Worksheet2. If I edit data in Worksheet2, it correctly
updates on Worksheet1. So far, so good.

However, if I delete a row in Worksheet2, that change does not show up in
Worksheet1. I get a #REF! error. If I add a row in Worksheet2, the added
row does not show up in Worksheet1, but all the cell references change row
numbers to reflect the new row number after the row was added.

What I really want to accomplish is that Worksheet1 is a single worksheet
with all the data from Worksheet2, followed by all the data from Worsheet3,
Worksheet4, etc so that Worksheet1 is always updated with any changes made on
the separate worksheets.

Am I expecting too much, or is it possible to do what I want to do?

--

Dave Peterson