Thanks for your response Bill. Let me try to clarify my question:

I have a large speadsheet made up of rows of subject titles and data. I am
trying to build a Report which only contains a third or so of the rows. So
let's say my Source is 4 columns wide by 100 rows long. My report will be 4
columns wide by only 25 rows - but the rows are not contiguous in the Source.
It might be row 1, then 7 and so on.

If possible, I would like to be able to copy the entire sheet to a new
worksheet, paste it as a link and then delete the rows from the Destination
that I don't need. But - I would also like to be able to add new rows in the
Source and be able to re-sort without having to worry about the Destination
report being altered.

Is this possible? I know that I can do this by Pasting each link
individually - but can I accomplish this globally?

Thanks for your help,
Pud.

"Bill Manville" wrote:

So to be clear, you want, say, B2 on Sheet1 always to reference X3 on
Sheet2 and B3 to reference X4 etc, so that if you sort the table on
Sheet2 or insert new rows or columns in Sheet2 the values in
Sheet1!B2:B100 are always the same as those in Sheet2!X3:X101 ?

In Sheet1!B2 put
=OFFSET(INDIRECT("Sheet2!X3"),ROW()-ROW(B$2),0)
and copy down to B100

If you know that row 2 on Sheet2 will always be above the first data
that you are interested in then

=OFFSET(Sheet2!$X$2,ROW()-ROW(B$2)+1,0)
would suffice

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup