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