Automatically update the name of student in comment bank, based on content of separate cell.

[mtaylor]

HI all,

Lets say I have the following comment in C1 in tab '8C':

Khadem has had a mixed year in Maths but has made progress nonetheless. A determined attitude is one of Khadem's many attributes and he should use this in a positive manner. Next year, Khadem should look at filling any gaps in his knowledge and look to push on, thus increasing his level.

I would like it to be the following:

<'Settings!' A2> has had a mixed year in Maths but has made progress nonetheless. A determined attitude is one of <'Settings!' A2>'s many attributes and <'Settings!' B2>should use this in a positive manner. Next year, <'Settings!' A2> should look at filling any gaps in <'Settings!' C2> knowledge and look to push on, thus increasing his level.

Where A2 in the settings tab is the student name
B2 is he or she depending on the student
C2 is his or her depending on the student

I just have no idea what the formula should be in the cells where the comments actually are

Thanks for looking

 

[Joe4]

This is actually a lot easier than you might think. Here is the key:
You want to combine range references (formulas) with hard-coded text.
Literal hard-coded text needs to be enclosed within double-quotes.
Range references (and any other formulas) MUST be outside of double-quotes (or else they will be treated as literal text.
Then, you combine each piece using the & symbol.

So, your formula should look like this:

=Settings!A2&" has had a mixed year in Maths but has made progress nonetheless. A determined attitude is one of "&Settings!A2&"'s many attributes and "&Settings!B2&" should use this in a positive manner. Next year, "&Settings!A2&" should look at filling any gaps in "&Settings!C2&" knowledge and look to push on, thus increasing his level."

I color-coded the range references in red and literal text in blue to make it easier to see.

 

[mtaylor]

Love it - thanks mate

 

[Joe4]

You are welcome.
Glad I was able to help!