Excel linked cells in Word - can you hard code or "F4" the link ?

[Marmot3292]

Hi All,

I have been searching this morning to see if it is possible to link a cell or row from Excel to a Word document, but to 'hard code' the cell/row, so that if I insert/delete rows above, it still references the same data? So the equivalent of F4'ing the cell in Excel but via an object link to word.

For example in the image below, I created a link to cell a2 which contains the value "888" which is what i wanted to reference in my word document. However if I add a row above and insert the value "45", the word document changes to reference 45 and I want to keep it as 888 despite the new row above. Is there any way to do this?

Essentially the real life example is i I have a spreadsheet that contains stock data, and I have multiple word reports for each stock. The spreadsheet is a working document and will change if I buy/sell new stocks, and I don't want to constantly re-reference the links.

The other work around is to have a 'working tab' which basically looks up each stocks data and new stocks are appended at the bottom only, but this will still run into issues when lines are deleted...

Any ideas?

Many thanks

 

[6StringJazzer]

I have been doing these types of links for several years and have not found Microsoft documentation that specifies how this works, but I can tell you my experience. It seems that Word does not understand the semantics or internals of how an Excel sheet is organized, it only has a cell range address, so there is no equivalent of using absolute addressing (incidentally, that won't even work from one Excel sheet to another; when you insert a row, all the references would be updated. In your example, a reference to $A$2 would be converted to $A$3 after a row insertion.).

The workaround is pretty much what you describe, although it seems like there would be a way to manage the insertions. It's hard to give a more specific response without seeing what your actual sheet looks like and what you want it to look like in Word.

 

[Marmot3292]

I have been doing these types of links for several years and have not found Microsoft documentation that specifies how this works, but I can tell you my experience. It seems that Word does not understand the semantics or internals of how an Excel sheet is organized, it only has a cell range address, so there is no equivalent of using absolute addressing (incidentally, that won't even work from one Excel sheet to another; when you insert a row, all the references would be updated. In your example, a reference to $A$2 would be converted to $A$3 after a row insertion.).

The workaround is pretty much what you describe, although it seems like there would be a way to manage the insertions. It's hard to give a more specific response without seeing what your actual sheet looks like and what you want it to look like in Word.

Thanks for your help on this. Good to get confirmation.