Zeros Appearing in Pasted Link After Changing First Sheet

I chose Paste Link from the Edit menu to paste a row of data from one worksheet into a second. After inserting a row into the first sheet above the linked range, I find that all the linked cells included in the second sheet have been filled with zeros.

How can I copy information from one worksheet into a second worksheet so that the second worksheet will not be affected by the insertion of rows above the range in the first sheet?

Response:

To copy information from one worksheet to another without later affecting the second with insertions into the first, do the following:

1. On the first sheet, define a name for the row of data that you want to link to the second sheet.

2. Activate the second sheet.

3. Highlight an area that has the same dimensions as the name-defined row of the first sheet, making sure that the leftmost cell is the active cell.

4. In the active cell, enter the following formula:

         =FIRSTSHEET!name
   

5. Enter this formula as an array using the following keys:

         CTRL+SHIFT+ENTER