Excel Paste Link - Insert row problem

[Guest]

I have a word document linked to an excel spreadsheet. The problem I am
having is that when I insert a row (in excel) above linked data on the
spreadsheet, it messes up the links. Not sure if that is clear so here is an
example of what I am talking about:

-Word document linked to cell A5 in Excel document
-I insert a row at the top of the spreadsheet, so now the info that was in
cell A5 is now in cell A6
-The problem is the word document is still linked to cell A5 instead of A6

Thanks in advance for your help

 

[Beth Melton]

External links are referenced using the R1C1 reference style which
makes them absolute. So for example the link in Word to cell A5 is
referenced as R5C1 and it will always link to R5C1. IOW, Word will not
automatically adjust the cell addresses in the link relative to their
original location.

If you are inserting new rows in the Excel worksheet then use a Named
Range (Insert/Name/Define) for cell A5 and use it for your link
instead of the cell address.

Then to update the link in Word:

- Press Alt + F9 to toggle the field codes
- Replace R5C1 in the link with your named range
- Press Alt + F9 to toggle off the field codes
- Select the link and press F9 to update

--
Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/