So why not do the lot in Word using, for example, formfields and field coding or content controls and macros?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropd, the reason it has to be done in excel first is because I require another team to fill in the variables for me. The spreadsheet itself is used to satisfy all necessary legal requirements we need to adhere to.

So far i've just got a cell off to the far right with the VLOOKUP stuff in it, where the template text will be held.

I was thinking a copy and paste special macro assigned to a button would work to copy the contents of the cell, and paste special (values) into a text box, which will allow the text to then be edited.

I tried to 'record a macro', but all it did was include the actual templated text within the macro, which means even if the variables are changed, the text won't.

After trying to watch some youtube videos and more googling, I've got this so far... I can get the Copy to work correctly, just don't know how to refer to the textbox...
I recorded a new macro and selected the text box, then typed random words into it just so i could try to figure out the code on how to refer to the textbox, but I seem to be stuck.

of itself, that doesn't mean you need to involve Excel - it could still all be done in Word.

That said, your users could type their supplemental info into column D on sheet1. The Data sheet could then use formulae like:
="You should wake up tomorrow morning at "&Sheet1!D2&"am."
="You should eat "&Sheet1!D2&" for breakfast tomorrow."
etc.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropd, appreciate yours and others' input so far.

I've figured out a way to resolve my issues. For anyone else that has looking for a solution to a similar problem, I simply output the text in 'hidden' cells (by that I mean i've just made the text output to cell AC2, AC3 etc). I then inserted an ActiveX button with the following macro:

Sub fillstrategy()
'
' fillstrategy Macro
Sheets("PP").TextBoxes("TextBox 12").Text = Sheets("PP").Range("AC2").Value
Sheets("PP").TextBoxes("TextBox 13").Text = Sheets("PP").Range("AC3").Value
Sheets("PP").TextBoxes("TextBox 14").Text = Sheets("PP").Range("AC4").Value

End Sub

This way the relevant text from the items selected in the drop down menu are auto-filled into the text boxes.

The users then read through the templated text, and update the areas that i've marked with ##XXXXXXXX##.

Just one last question: Is there any way to highlight the ##XXXXX## sections of the templated text? I've highlighted the sections in red in the 'raw data', but when the text pulls through from the INDEX formula, it comes through all black, without the ##XXXX## sections highlighted in red.

And we require the use of excel as our client's data is automatically fed into excel which then auto-populates relevant sections. Appropriate financial calculations are then performed using that data. The outcome of this background research/calculations are then outputted to a templated word document.

Excel formulae cannot capture and output font attributes; only the raw text/values.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]