I am trying to template specific text to be used in a Word document.

I have set it up so that depending on what the user selects from a drop down box, the relevant templated text appears for that selection.

However, the templated text has areas which the user needs to manually go in and change to what is appropriate for the situation.

How would I go about making this happen? The best thing that I can think of is to have the formula off to the side, and make the user copy and paste the text the formula returns you into a text box, where you can then edit the text.

I have also noticed the colour coding of specific parts of the text (the part the user has to manually change) doesn't come through with the formula.

Attached Images

example.png (12.0 KB, 27 views)

Attached Files

example.xlsx (10.1 KB, 9 views)

Just split your vlookup into parts and concatenate. For the first one, have one vlookup grab text in the first column and then another to grab the time. Then your formula for wakeup will be...

=VLOOKUP(B2,Data!$A$1:$C$5,2,FALSE) & VLOOKUP(B2,Data!$A$1:$C$5,3,FALSE)

Sleep will be similar. The meals, since the variable data is in the middle, requires 3 vlookups.

Thanks for the reply gebobs, but column B is variable. The sequence will not necessarily be the same. For example, row 2 might be 'Lunch', row 3 might be 'Sleep' and that's it in some scenarios. As the variable part of the sentence changes depending on what is selected and in what order, the formula you have suggested won't account for that.

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]