How to create a form letter in Word that contains the data from an Excel Pivot table?
Task (customer):
I have a list of addresses that I have in Excel. For easier filtering, I changed the data to a pivot table.
Now I would like to create serial letters from the filtered Excel Pivot addresses in Word.
Solution:
You can easily create form letters from Excel in Word. However, when linking data sources from Excel, only tables and named namespaces are offered for selection.
So you have to put in Excel the address range as a name variable and then connect this in Word.
Manual:
Excel and Word
1) Prepare Excel addresses
Word has address data as raw data. In this example, the data is entered manually.
From this data, a classic pivot table was created.
In Pivot please change the mode to Classic view and switch off all partial results of the cells.
To create the filtered addresses, you must now mark the area that should be the output for Word.
In Pivot you can easily filter the data in the header line. Like here the field: city
Name address range:
From the bottom right, mark the area up to the upper left corner.
Here from cell 24G to A4
Then enter the address in the upper left corner.
As a result, the area of A4: G27 will be found under address array arr addresses in the future.
The area should be chosen large enough. For post-processing you can use the address range at any time
under Menu -> Formulas -> Ribbonbar -> Name Manager
To be changed
2) Create Word Mail Merge
In Word you create a new document and select: Menu-> Shipments-> Select recipient
And then use existing list ..
Then select yourself up to the Excel file and click on the Open button
Here you will find the selection arrAdressen
Then you have to remove the empty entries in the future, by adjusting the import filter.
On Menu-> Shipments-> Edit recipient list
And then open the filter on last name
Then select the selection on (not empty). As a result, only real entries will be imported in the future.
This shrinks the result list to the Pivot Filtered List.
Now you just have to insert the filter fields in the Word form letter and then examine the preview
In the preview, only the reduced mail merge selection will appear