Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2020, 02:55 PM
Cantora Cantora is offline Linking from Excel - Help auto-resizing tables Windows 10 Linking from Excel - Help auto-resizing tables Office 2019
Novice
Linking from Excel - Help auto-resizing tables
 
Join Date: Jan 2020
Posts: 6
Cantora is on a distinguished road
Default Linking from Excel - Help auto-resizing tables

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?
Reply With Quote
  #2  
Old 02-16-2020, 06:47 PM
macropod's Avatar
macropod macropod is offline Linking from Excel - Help auto-resizing tables Windows 7 64bit Linking from Excel - Help auto-resizing tables Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Give your Excel range a name, then edit the LINK field's address to point to that name. 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]
Reply With Quote
  #3  
Old 02-19-2020, 10:07 PM
Cantora Cantora is offline Linking from Excel - Help auto-resizing tables Windows 10 Linking from Excel - Help auto-resizing tables Office 2019
Novice
Linking from Excel - Help auto-resizing tables
 
Join Date: Jan 2020
Posts: 6
Cantora is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Give your Excel range a name, then edit the LINK field's address to point to that name. 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.
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
Reply With Quote
  #4  
Old 02-19-2020, 10:16 PM
macropod's Avatar
macropod macropod is offline Linking from Excel - Help auto-resizing tables Windows 7 64bit Linking from Excel - Help auto-resizing tables Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please Help: Linking pivot tables from excel Cantora Word Tables 0 01-13-2020 09:21 PM
Linking from Excel - Help auto-resizing tables Resizing Pictures in Cell Tables Hit or Miss MikeWhite Word Tables 2 03-12-2019 03:04 AM
Linking from Excel - Help auto-resizing tables Auto "import" data from word tables into excel Kalü Excel Programming 3 11-26-2018 02:51 PM
Word tables resizing paulyidaho Word Tables 4 07-30-2018 08:59 AM
Linking Excel Pivot Tables in a Word Document wmarsh3561 Word Tables 0 11-17-2009 06:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft