Paste Link
 

I have created a worksheet in Excel 03. I am trying to link a large number
of the cells to another worksheet in the same workbook. When I use Paste
Link to link 1 cell only I get an absolute reference ($). When I try to link
numerous cells at once - I get the exclamation mark (!)and the cell rederence
(ie !D4). Then if I add rows and re-sort the source worksheet - the
destination will no longer contain the proper referenced links.

Question - how do I Paste Link for a block of cells and still maintain an
absolute reference so that I can change the Source and be confident that the
Destination Worksheet will be properly changed as well?
--
Many thanks,
Pud

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

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

I would approach it differently
I would have a column in the main table in which I would mark the
"interesting" rows.
Then I would use Data / Filter / Advanced Filter / Copy to another
location to copy the interesting rows to the other worksheet.

I would avoid using links if you plan to sort the Source sheet since
that will not work.

If there is a unique key in column A of the Source table and you can
make a list of the keys which are interesting then you could use
VLOOKUP formulas to pick up the other values from the relevant row.

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

Many thanks Bill - I will give it a try!

"Bill Manville" wrote:

I would approach it differently
I would have a column in the main table in which I would mark the
"interesting" rows.
Then I would use Data / Filter / Advanced Filter / Copy to another
location to copy the interesting rows to the other worksheet.

I would avoid using links if you plan to sort the Source sheet since
that will not work.

If there is a unique key in column A of the Source table and you can
make a list of the keys which are interesting then you could use
VLOOKUP formulas to pick up the other values from the relevant row.

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