Linking Tables Excel to Word - Question

[ProgEval]

I am paste linking tables in Excel to Word. Is it possible to change a link to include an extra column while in Word? For instance if my linked table includes columns A,B and C. Later I add a Column D to the table. Can I change something in Word so that it picks up that extra column?

Thanks in advance.

 

[SkipVought]

Hi,

" Later I add a Column D to the table"

That statement is a red flag to me. Why would the STRUCTURE of a table be so dynamic? Adding a column is to a table is a design change, not a DATA change. Is your added column really another data element?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ProgEval]

When I add a column, I am adding another quarter's worth of data. It may just be that I have to re-paste all those linked tables each quarter. Just thought there may be a easy way to change the table already linked in Word.

 

[SkipVought]

"...quarter's worth of data."

Table structure is not supposed to change with DATA.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ProgEval]

This is a table in excel, not a relational table in access.

 

[Welshbird]

Prog - I think what Skip is aiming at is some kind of normalised structure in Excel for you.

So, I'm guessing now you might have:

Prod Q1 Q2 Q3
Socks 345 345 356
Shoes 789 987 456

and what Skip would suggest (as would I) is:

Prod Qtr Value
Socks 1 345
Socks 2 345
Socks 3 356
Shoes 1 789
Shoes 2 987
Shoes 3 456


So, the STRUCTURE remains the same, just more data is added.

(NB, A pivot table could happily report on this for you in the format you need, but the underlying data would remain in a constant format)

Hope this helps.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ProgEval]

I thank both of you for your input.

It appears your suggestion would still run into the same problem.

Prod Qtr Value
Socks 1 345
Socks 2 345
Socks 3 356
Shoes 1 789
Shoes 2 987
Shoes 3 456

If my table is in this format in Excel, then when I add 4th Quarter's data I would add rows instead of columns. Could I easily change my linked table in Word to account for those added rows?

I hope this makes sense. Thanks.

 

[SkipVought]

If it's linked, you have the rows.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ProgEval]

Right now if I have a table in Excel that is Columns A, B and C and Rows 1-10. If I copy and paste link that table, it will only show those rows and columns. If rows are added, it will still only show Rows 1-10, no matter what changed in Excel. That is where I was wondering if there is a way to change the link to inlclude more rows/columns. Am I doing something wrong when I copy and paste link?

I have always copy and pasted the tables for each report. I was hoping to avoid that by just changing something in the linked table in word instead of re-copying and pasting.

 

[SkipVought]

The LINK does not change. How you display the link, ie the size of the window, determines how much is displayed.

Double-click the link to open.

Resize to show all rows.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

Simpler - Just Update Links - Edit > Links - Update Now

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ProgEval]

Skip,

The link I currently have will show only the cells copied, no matter the size. When I double click on the linked table, it takes me to the highlighted cells in excel, but I can't figure out how to get more rows.

I am clearly doing something wrong along the way.

 

[SkipVought]

Instead of copying, Insert a link from the Insert menu.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[macropod]

Hi ProgEval,

It's easiest to change the linked range if you use named ranges in Excel. Then all you need to fo is change the range the name applies to.

Failing that, you can easily change the linked range from Within Word, by editing the field codes. To do this, select the linked Excel object in Word and press Shift-F9. You should see something like:

LINK Excel.Sheet.8 "C:\\My Documents\\My File.xls" "Sheet1!R1C1:R16C32" \a \p

All you need to do now is update the source range, then press F9 to refresh the link.

Cheers

[MS MVP - Word]

 

[ProgEval]

That is what I was looking for. Thanks macropod!