Elegant / better solution to mail merge file cover problem
Hi all,
I'm returning to a problem I've been working on and off with for a while or so, convinced that there is a better way to do what I'm looking to do, so hopeful someone here is a bit smarter than me.
I am looking to produce a standard file cover, as per the attached example which contains all the usual items a file cover would need. We currently have a mix of similar but different ones on the go between 15 people so I would love to get some standard and save people some time.
The first attachment is the word document I wish to use, the second attachment is an excel sheet, which contains the 'mail merge data'. The sheet has three tabs.
ApplicationSheetData which is where the data is entered, it contains a VLOOKUP formula which reads the entered reference number and returns the relevant information (highlighted red on the page)
CMS_Codes, the reference list for the Vlookup in the first tab,
Mail_Merge is the final tab and simply copies information from ApplicationSheetData and is the source of the mail merge linked to my word document.
The first problem is that if a particular data line does not return info, for example an address line, then a zero shows rather than a blank line.
The second problem is that when the merge runs if one of the lines of data is too long it carries over in the word doc and cannot be read. I have tried 'wrapping' the box but without any luck.
My final problem is the biggest in that despite my best efforts the whole thing feels a bit convoluted and long winded. Is there anyway that the excel components can be incorporated in word in some way??
By this I mean would word be able to recognise the reference number and carry out some sort of VLOOKUP equivalent which is currently taking place in Excel???
Any help or tips to simply this thing will, as always be greatly received.
Thanks for reading, Ballst
|