#1   Report Post  
Old April 11th 05, 01:13 PM
akpud
 
Posts: n/a
Default 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

  #2   Report Post  
Old April 11th 05, 02:22 PM
Bill Manville
 
Posts: n/a
Default

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

  #3   Report Post  
Old April 11th 05, 02:56 PM
akpud
 
Posts: n/a
Default

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


  #4   Report Post  
Old April 11th 05, 11:39 PM
Bill Manville
 
Posts: n/a
Default

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

  #5   Report Post  
Old April 12th 05, 12:09 AM
akpud
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"paste special" "paste link" lssweatt Excel Discussion (Misc queries) 2 March 29th 05 08:51 PM
Paste Link enters a 0 into the cell where I paste. How do I elemin UNR Excel Discussion (Misc queries) 4 March 28th 05 01:54 AM
Using paste link infromation in cell to hyperlink to source cell? Wayne Excel Worksheet Functions 7 February 27th 05 07:38 PM
Paste Link - Cell Comments get lost Wayne H Excel Worksheet Functions 2 February 26th 05 11:51 PM
Paste Link and Templates Emerald MR Excel Worksheet Functions 0 November 9th 04 07:22 PM


All times are GMT +1. The time now is 05:23 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017