Hi All,

I have a word document that links to excel for all it's tables & graphs.
Graphs are easy, but the link for tables seems completely static: If I add a new line in excel I have to change the word formula with the extra lines.

Does anyone know how to make the links resize automatically? Is there a way to link to the Table or Pivot graph rather than a set range?

Give your Excel range a name, then edit the LINK field's address to point to that name. You can toggle the field code display on/off by pressing Alt-F9. For example:
{LINK Excel.Sheet.8 "C:\\Users\\UserName\\Documents\\Workbook.xls" "Sheet1!R1C1:R10C6"}
becomes:
{LINK Excel.Sheet.8 "C:\\Users\\UserName\\Documents\\Workbook.xls" "Sheet1!Table"}
Now, whenever the range spanned by the name changes in Excel, the link in Word will update to reflect the change.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macro,

Thanks so much for this. It wasn't a working solution unfortunately, but it did lead me to search for new terms which found me the right solution.

For tables: you need to define a name & for pivots you need to define an indirect name to an expanding formula (e.g ="A2"&COUNTA(D))

thanks so much for your help with this

Yes, it's possible define names within Excel so they update dynamically (as you have found), thus allowing them to work with LINK fields in Word that point to the named range.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]