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

mtaylor

Board Regular
Joined
May 1, 2013
Messages
72
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
=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.
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,502
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Forum statistics

Threads
1,176,098
Messages
5,901,381
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top