[SueK]

I have created a link between Excel and Word that anytime I change or update
a cell in Excel, it updates a specific spot in Word. I did this by copying
the cell in Excel and then paste special, paste link as unformatted text in
Word. It worked great until we deleted some rows in Excel and discovered
that the links in Word stays with the row it was linked to so it now updates
with the wrong information. Is there a way to preserve this link so rows can
be deleted or sorted in Excel and the correct information will be updated in
my Word document?

Thanks for any help!

[CyberTaz]

The easiest way I know of:

Assign a Range Name to the target cell, then recreate the link or edit the
link in your Word doc to replace the cell reference with the range name. If
you call the range "bubba" for example, your link field in Word would look
something like:

{ LINK Excel.Sheet.8 "WkbkName" "SheetName!bubba" \a\p }

Note: In order for the field code to display (Alt+F9) it should be set to
InLine With Text rather than as a floating object.


Another way: In any cell (we'll call it "proxy") of the XL workbook create a
formula link that points to the desired target cell wherever it happens to
be at the time. The proxy will stay updated when the actual target cell
shifts to a different grid position. Then recreate your link from Word to
the proxy cell.

IOW - Say your target cell is L25... In some other cell of the worksheet
(proxy) type in =L25 which will establish a formula link to that cell.
[Note: If proxy is not on the same sheet as the target cell the formula link
must include the sheet name as well as the cell reference. Easiest way to
get it right is to just type the = then click the target sheet tab, then
click the target cell & press Enter.] Then link from Word to proxy rather
than to L25.

Other options may be available as well so you may want to ask in the
microsoft.public.excel newsgroup to see what they have to offer.
--
HTH |:)
Bob Jones
[MVP] Office:Mac

"SueK" wrote in message
...
I have created a link between Excel and Word that anytime I change or
update
a cell in Excel, it updates a specific spot in Word. I did this by
copying
the cell in Excel and then paste special, paste link as unformatted text
in
Word. It worked great until we deleted some rows in Excel and discovered
that the links in Word stays with the row it was linked to so it now
updates
with the wrong information. Is there a way to preserve this link so rows
can
be deleted or sorted in Excel and the correct information will be updated
in
my Word document?

Thanks for any help!

[SueK]

Thank you for responding.

I tried both ways but anytime I rearrange the rows in Excel, it updates to
whatever is on the row.

Ex.

r2a2 - 2/21/07
r2b2 - New Customer Strategy
r2c2 - Initiative 2
r2d2 - George - leader

r3a3 - 2/27/07
r3b3 - Current Customer Strategy
r3c3 - Initiative 1
r3d3 - Bob - leader

Each of these rows have their own corresponding Word doc that I link the
cells to so anytime the cell gets updated, it updates the Word doc. But when
I re-sort, the Word doc associated with New Customer strategy gets updated
with the Current customer strategy info. I tried your first option which
made a lot of sense because I've used that before. But, when I re-sort, the
named cells stayed where it is and so it still updated with the wrong info.

I tried the proxy option but that didn't work either because the proxy cell
is referring to cell r3b3 and when I re-sort the original, whatever is in
cell r3b3 is the one it's picking up.

Am I doing both your suggestions wrong? This is driving me crazy!!

Thanks for any help!
"CyberTaz" wrote:

The easiest way I know of:

Assign a Range Name to the target cell, then recreate the link or edit the
link in your Word doc to replace the cell reference with the range name. If
you call the range "bubba" for example, your link field in Word would look
something like:

{ LINK Excel.Sheet.8 "WkbkName" "SheetName!bubba" \a\p }

Note: In order for the field code to display (Alt+F9) it should be set to
InLine With Text rather than as a floating object.


Another way: In any cell (we'll call it "proxy") of the XL workbook create a
formula link that points to the desired target cell wherever it happens to
be at the time. The proxy will stay updated when the actual target cell
shifts to a different grid position. Then recreate your link from Word to
the proxy cell.

IOW - Say your target cell is L25... In some other cell of the worksheet
(proxy) type in =L25 which will establish a formula link to that cell.
[Note: If proxy is not on the same sheet as the target cell the formula link
must include the sheet name as well as the cell reference. Easiest way to
get it right is to just type the = then click the target sheet tab, then
click the target cell & press Enter.] Then link from Word to proxy rather
than to L25.

Other options may be available as well so you may want to ask in the
microsoft.public.excel newsgroup to see what they have to offer.
--
HTH |:)
Bob Jones
[MVP] Office:Mac

"SueK" wrote in message
...
I have created a link between Excel and Word that anytime I change or
update
a cell in Excel, it updates a specific spot in Word. I did this by
copying
the cell in Excel and then paste special, paste link as unformatted text
in
Word. It worked great until we deleted some rows in Excel and discovered
that the links in Word stays with the row it was linked to so it now
updates
with the wrong information. Is there a way to preserve this link so rows
can
be deleted or sorted in Excel and the correct information will be updated
in
my Word document?

Thanks for any help!

[CyberTaz]

Hi Sue -

Your problem is that the problem isn't quite what you stated it to be in the
first post, combined with my overlooking the the word "sort"

There's a big difference between *Inserting/Deleting* rows and *Sorting*
them. When you sort the _content_ shifts from one row to another, but the
_rows_ (and therefore the references to the cells within them) do not
change. IOW, sorting rearranges the content of the grid, inserting/deleting
rows/columns rearranges the grid itself. Links follow the grid.

What you're asking for is that the linked object locate the right content no
matter where it winds up in the source file, and I have no idea whether that
can even be done. If so, I believe it would have to be something constructed
in the workbook - probably involving VBA. For that I'll have to refer you to
the code junkies in one of the Excel newsgroups Most likely:

microsoft.public.excel.links

or

microsoft.public.excel.worksheet.functions
--
Regards |:)
Bob Jones
[MVP] Office:Mac

"SueK" wrote in message
...
Thank you for responding.

I tried both ways but anytime I rearrange the rows in Excel, it updates to
whatever is on the row.

Ex.

r2a2 - 2/21/07
r2b2 - New Customer Strategy
r2c2 - Initiative 2
r2d2 - George - leader

r3a3 - 2/27/07
r3b3 - Current Customer Strategy
r3c3 - Initiative 1
r3d3 - Bob - leader

Each of these rows have their own corresponding Word doc that I link the
cells to so anytime the cell gets updated, it updates the Word doc. But
when
I re-sort, the Word doc associated with New Customer strategy gets updated
with the Current customer strategy info. I tried your first option which
made a lot of sense because I've used that before. But, when I re-sort,
the
named cells stayed where it is and so it still updated with the wrong
info.

I tried the proxy option but that didn't work either because the proxy
cell
is referring to cell r3b3 and when I re-sort the original, whatever is in
cell r3b3 is the one it's picking up.

Am I doing both your suggestions wrong? This is driving me crazy!!

Thanks for any help!
"CyberTaz" wrote:

The easiest way I know of:

Assign a Range Name to the target cell, then recreate the link or edit
the
link in your Word doc to replace the cell reference with the range name.
If
you call the range "bubba" for example, your link field in Word would
look
something like:

{ LINK Excel.Sheet.8 "WkbkName" "SheetName!bubba" \a\p }

Note: In order for the field code to display (Alt+F9) it should be set to
InLine With Text rather than as a floating object.


Another way: In any cell (we'll call it "proxy") of the XL workbook
create a
formula link that points to the desired target cell wherever it happens
to
be at the time. The proxy will stay updated when the actual target cell
shifts to a different grid position. Then recreate your link from Word to
the proxy cell.

IOW - Say your target cell is L25... In some other cell of the worksheet
(proxy) type in =L25 which will establish a formula link to that cell.
[Note: If proxy is not on the same sheet as the target cell the formula
link
must include the sheet name as well as the cell reference. Easiest way to
get it right is to just type the = then click the target sheet tab, then
click the target cell & press Enter.] Then link from Word to proxy rather
than to L25.

Other options may be available as well so you may want to ask in the
microsoft.public.excel newsgroup to see what they have to offer.
--
HTH |:)
Bob Jones
[MVP] Office:Mac

"SueK" wrote in message
...
I have created a link between Excel and Word that anytime I change or
update
a cell in Excel, it updates a specific spot in Word. I did this by
copying
the cell in Excel and then paste special, paste link as unformatted
text
in
Word. It worked great until we deleted some rows in Excel and
discovered
that the links in Word stays with the row it was linked to so it now
updates
with the wrong information. Is there a way to preserve this link so
rows
can
be deleted or sorted in Excel and the correct information will be
updated
in
my Word document?

Thanks for any help!