linking tables in Excel to a Word document

[tom.suters]

I use Office 2010. when i link an excel file to a word file (e.g. for e-mail ditribution lists) i can only select from the names of all tabs in he excel file, not the name of a table i created in that tab. if my table does not start at row 1 but e.g. at row 5 (with some other text and stuff in rows 1 to 4) i cannot use the names of the columns in my table to filter. etc. All columns are named "Fn". This seems silly. what do i do wrong ?

thx for your time and help.

[HansV]

Welcome to Eileen's Lounge!

You can select your table in Excel, then give it a name by clicking in the address box on the left hand side of the formula bar and typing a name (without spaces), then pressing Enter.

When you import or link in Access, you can choose between "Show Worksheets" and "Show Named Ranges".
Click "Show Named Ranges" and select the name that you assigned.

x1180.png

[tom.suters]

Hi Hans, thx for the quick reply but i link to Word (not Access) and after I have selected the excel file I do get to select from a list of named ranges but I only see the names of the excel tabs plus things <tabname>$printArea, etc. but not the name i gave to my table. appreciate your comment.

Perhaps i should state more exact: i use the home-office version of Office 2010. Sure hope that that is not the reason...

At some point i also remember to have seen a named range like <tabname>_xlnm#_filterdatabase# that did work but that has disappeared for some strange reason.

[HansV]

Sorry, I didn't read your question carefully enough.

You have to name your range explicitly - mail merge doesn't recognize table objects in Excel sheets.

The sheet shown in the screenshot below contains both an Excel table object and a named range, but only the named range is listed, not the table object.

x1181.png

[StuartR]

When I inserted a Link to an Excel 2010 object from Word 2010, it didn't offer me the option to select a named range.
When I then right clicked the linked Excel object in Word and selected Edit Link I was able to specify the source range.

[tom.suters]

Hans,

thx that explains. but how do I create a named range in an Excel sheet/tab? one that does not start in row 1 but can be anywhere on the worksheet and be of any size?

Btw, does the (apparently Office defined) named range <tabname>_xlnm#_filterdatabase# mean anything to you? This range did the trick as well... but i don't see it in the list of named ranges anymore. Did see it at some point durung my efforts to make it work, maybe it was from an excel file rom older Office version?).

Tom

[HansV]

To assign a name: select the table, including the header row.
Click in the address box on the left hand side of the formula bar.
Type the name, then click Enter.

x1182.png

Alternatively, activate the Formulas tab of the ribbon, and click Define Name in the Defined Names group.

x1183.png