Link OfficeConnect Values in a Paragraph
In addition to linking full tables or other sets of multiple cells from Excel to Word, you can link individual values and phrases within your narrative. For example, you can link report dates and specific dollar values in selected sentences or paragraphs.
You can even link and update words that correspond with your numbers, for example, “increased” and “decreased,” or “favorable” or “unfavorable.”
When you update your report for the next period, not only are the table values refreshed, but the individual dates, values, and key words are refreshed as well.
As with the tables, to make this work you must have the dates, values, and key words already set up as named ranges in the OfficeConnect for Excel workbook.
Read through your report in Word and determine the dates and numbers you want to link to data in your OfficeConnect for Excel workbook. Make sure you have named ranges in Excel for those dates and numbers. If necessary, go back to your Excel workbook and create them, and then save the file. Then return to your Word file, and in the OfficeConnect links pane, on the Workbook Links tab, right-click the Excel filename, and then click Refresh Source. Any newly created named ranges appear on the tab. Now you’re ready to start linking values in your narrative.
To link values in a narrative paragraph:
In your OfficeConnect for Word document, select the values you want to link with OfficeConnect data.
You can select just a few words or multiple paragraphs. It’s a good idea to select a few words on either side of the value you want to link, so you can see the context later.
Right-click the selected text and then click View Paragraph Links on the shortcut menu.
Or, on the OfficeConnect tab, in the External Data group, click the arrow under Link, and then click Paragraph Links.
The Paragraph Links dialog box appears. Any values included in the selected text are listed separately in the Value column, while the text surrounding the value appears in the Associated Text column.
In the OfficeConnect links pane, click the Workbook Links tab and find the named range you want to link to the value in the selected text showing in the Paragraph Links dialog box.
Drag the named range from the Workbook Links pane to the heading of the row showing the value and associated text you want to link.
The current value of the named range replaces the value in the selected text.
If you need to link additional values in the Paragraph Links dialog box, repeat steps 3 and 4 for those values.
As you drag the named ranges, the values change in the Paragraph Links dialog box as well as in your selected text in the document.
If your selected text contains multiple values (dates, currency, or other numbers), the Paragraph Links dialog box shows a separate row for each value. You do not have to link every value shown. Just apply links where you want them.
The selected values are now linked with the selected named ranges from your OfficeConnect for Excel workbook. Whenever the values in the Excel workbook changes and you refresh the Word document, the linked values are updated.
Link OfficeConnect Words in a Paragraph
Just as you can link dates, currency, and other values in your narrative text, you can link and update qualitative words that help describe or interpret your numeric performance and that can change across reporting periods as do the numbers themselves. Examples might include “positive,” “negative,” “increase,” and “decline.”
To return a qualitative word value of something like “increase” or “decrease,” you need to set up a logical test formula in your linked OfficeConnect for Excel spreadsheet. Such a formula might look similar to the following:
Remember to save the cell containing the logical test formula as a named range so you can link to it in your OfficeConnect for Word document.
To link words or phrases, you replace the words you want to change with a temporary placeholder number so that OfficeConnect treats the word or phrase as a value. Then you link a named range word from your OfficeConnect for Excel workbook that’s associated with numeric performance.
To link words that reflect numeric performance in a narrative:
In your OfficeConnect for Word document, replace the words you want to change to an OfficeConnect link with any number of your choice, for example, 111 or 1234.
It doesn’t matter what this number is, because it’s just a temporary placeholder and will be replaced with the linked text. It only needs to be a number you can recognize as the placeholder when you see it listed in the Paragraph Links dialog box.
Select the text you want to link, including a few words on either side of your placeholder values so you can see the context later when you’re linking.
As with values, you can select just a few words or multiple paragraphs. OfficeConnect will identify any values in the selected area, including your placeholder values.
Right-click the selected text, and then click View Paragraph Links on the shortcut menu.
The Paragraph Links dialog box shows a row for any values in the selected text, including your placeholder values.
From the Workbook Links tab in the OfficeConnect links pane, click the named range that represents the word or phrase you want, and drag it to the heading of the row showing the placeholder value and associated text to be linked.
The current word or phrase for the named range replaces the placeholder value in the selected text.
The words or phrases you specified for linking are updated in your document.