Dynamic Link Excel 365 to Word

[SeliM]

Good morning,
I have an Excel named range (ComNamesforList) that will provide all company names that have submitted a tender for works.

I have a report template that holds variety of management information and the need for a full list of companies that submitted tenders.

I can use the copy special past link process to link the list however I would like the list to be dynamic to contract or expand depending on the number of tenders received.

I have tried a variety of (guess) ways to improvise and failed.

Would you have any advice to assist please.

Many thank for reading my post and in advance for your advice.

Mel

 

[6StringJazzer]

First define a named range for the range you want to copy.

Copy the named range and Paste Special, As Link

If you add rows or columns, update the definition of the named range to include the added rows/columns.

In the Word document select the table then hit F9 to refresh the table.

It's not 100% automatic but it avoids having to copy & paste each time.

 

[SeliM]

Thanks Jeff
What seems so straight forward frequently can be the most frustrating.
I have tried many formats and followed your advice and cannot get the table inserted - I get the address of the named range instead!

I created a simple named range using - ="Sheet1!$B$3:"&ADDRESS(COUNTA(B:B)+2,3,1) at $E$3

Used Formulas Name Manager to create the name (=indirect($E$3)) - Range is called "Table"

attempts:
1. Copied the name (Table), to word using paste - there is no option for paste special only - Paste unformatted text

2. Copied the named range formula (as above) at address $E$3 - used paste special, link and get "Sheet1!$B$3:$C$14"

3. Copied named range name (Table) and used insert object, Text from file, named range - Table - word cannot find the range of data

I cannot find where I am going wrong. Any help much appreciated.

Mel

 

[6StringJazzer]

This isn't going to work with a range named using INDIRECT. The description in your first post sounds like there is no need for INDIRECT.

The way to do this is name a static range, then select that range and copy it. If you copy a range that is defined using INDIRECT (or any other dynamic range), Excel will not realize that this range is the same one you named.

You need to define a named range that is statically determined. That is, when you select the cells in the range, you should see the name of the range in the address box.

Your named range needs to be

=Sheet1!$B$3:$C$14

As I said, when you add/remove rows you will have to manually update the formula for the named range, but it saves you from having to copy and paste it again into Word.

 

[SeliM]

Thanks very much for all your work on this Jeff, guess the indirect problem shows my level of competence.

I'll see how I go

R
M

 

[Macropod]

I have an Excel named range (ComNamesforList) that will provide all company names that have submitted a tender for works.

I have a report template that holds variety of management information and the need for a full list of companies that submitted tenders.

I can use the copy special past link process to link the list however I would like the list to be dynamic to contract or expand depending on the number of tenders received.

Simple:
1. Copy the named range from Excel and paste it into Word using Paste Special>Paste Link and choose your desired paste format.
2. Select the pasted range in Word and press Shift-F9 to expose the field code, which will look something like:
{LINK Excel.Sheet.12 "C:\\FilePath\\FileName.xlsx" "WorksheetName!R70C1:R75C10" \a \p}
3. Replace the cell range (R70C1:R75C10) in the above example, with your range name, so that you get
{LINK Excel.Sheet.12 "C:\\FilePath\\FileName.xlsx" "WorksheetName!ComNamesforList" \a \p}
4. Press F9 to update the field display
From now on, the displayed range in the document will expand/contract to match the named range in Excel.

 

[6StringJazzer]

Have you tried this when the named range is defined using INDIRECT?

 

[Macropod]

No. I'm simply addressing the question as originally posed, which doesn't suggest INDIRECT was in use. That only seems to have become part of the issue after you introduced it - and which you later seemed to indicate wasn't needed.

 

[6StringJazzer]

OP introduced use of INDIRECT in post #3. I replied that won't work for pasting a link that adjusts dynamically. Other than that your solution seems to do pretty much what I described, although my version does not require editing the link code. If you select the range, and the name of the range shows up in the Range box, you can copy/paste link.

 

[SeliM]

Good morning
I thought I would let you know how I think I’ve got the system to operate consistently.

1. Used insert Table for the existing table of data
2. Created a range address using static commencement say Table 3 viz =“‘Table 3’!$A$4:”&address(counta($B:$B)+3,4,1) - mightn’t have that quite right but I’m sure you’ll get the strategy
4. Under Formulas, named ranges created a named range Table3 with an indirect reference to the address in 3 above
5 copied the table as exist pasted to word document with link as Microsoft image
6. Selected image Shift F9 and replaced the static range with Table3
7 Shift F9 to reveal the table

Have tested a couple of times - seems sound and seems to work

Just thought I’d share

R
Mel