I would like to automatically compose a comment for a cell based on the contents of another cell on a different worksheet. I realize this will require VBA, which is outside my skill set. I searched this forum and the Web and found several potentially relevant posts, but I was unable to successfully adapt those examples either due to problems with the posted code or (more likely) my ignorance.
Seems like this should be fairly straightforward....
On a worksheet named "Define", there is a small table that occupies C11:E20. In C11:C20, there are short text fields that are used as labels for rating elements (e.g. "Element1" in C11, "Element2" in C12, etc). In D11:D20, there are longer text fields that provide a longer name for the corresponding labels in C11:C20 (e.g. "Element 1 - Long Term Factors" in C11, "Element 2 - Short Term Factors" in C12, etc). In E11:E20, there are longer text fields that describe those corresponding elements in detail (e.g. "Element 1 is comprised of blah, blah, blah..." in E11, "Element 2 includes yadda, yadda, yadda..." in E12, etc).
On a worksheet named "Classify", there is a large table that uses those short labels as a column headers in K18:T18 (e.g. K18 = Define!D11, rendering as "Element1", L18 = Define!D12, rendering as "Element2", etc). Note the transposed orientation between the two worksheets.
I would like to automatically create/link comments for cells K18:T18 on the "Classify" worksheet that replicate the corresponding element's description text from the "Define" worksheet. That is, I want the comment for Classify!K18 (whose cell value is "Element1", matching what's in Define!C11) linked to display the text that's in Define!E11 ("... blah, blah, blah"), Classify!L18's comment linked to Define!E12, etc.
Bonus items:
1. It would be great if those comments were automatically updated if their descriptions (Classify!E11:E20) are edited.
2. Given my arrangement, the description text for the comments could be extracted via Index & Match technique rather than simply referring to an absolute cell reference.
3. It would be great if the comments were composed of the concatenation of the long name (in bold font), followed by the description text (normal font) on the line below. For example, the comment in Classify!K18 rendered as
I can forgo the bonus items if they're tricky.
Thanks in advance!
SDL
Seems like this should be fairly straightforward....
On a worksheet named "Define", there is a small table that occupies C11:E20. In C11:C20, there are short text fields that are used as labels for rating elements (e.g. "Element1" in C11, "Element2" in C12, etc). In D11:D20, there are longer text fields that provide a longer name for the corresponding labels in C11:C20 (e.g. "Element 1 - Long Term Factors" in C11, "Element 2 - Short Term Factors" in C12, etc). In E11:E20, there are longer text fields that describe those corresponding elements in detail (e.g. "Element 1 is comprised of blah, blah, blah..." in E11, "Element 2 includes yadda, yadda, yadda..." in E12, etc).
On a worksheet named "Classify", there is a large table that uses those short labels as a column headers in K18:T18 (e.g. K18 = Define!D11, rendering as "Element1", L18 = Define!D12, rendering as "Element2", etc). Note the transposed orientation between the two worksheets.
I would like to automatically create/link comments for cells K18:T18 on the "Classify" worksheet that replicate the corresponding element's description text from the "Define" worksheet. That is, I want the comment for Classify!K18 (whose cell value is "Element1", matching what's in Define!C11) linked to display the text that's in Define!E11 ("... blah, blah, blah"), Classify!L18's comment linked to Define!E12, etc.
Bonus items:
1. It would be great if those comments were automatically updated if their descriptions (Classify!E11:E20) are edited.
2. Given my arrangement, the description text for the comments could be extracted via Index & Match technique rather than simply referring to an absolute cell reference.
3. It would be great if the comments were composed of the concatenation of the long name (in bold font), followed by the description text (normal font) on the line below. For example, the comment in Classify!K18 rendered as
Element 1 - Long Term Factors:
Element 1 is comprised of blah, blah, blah..
Element 1 is comprised of blah, blah, blah..
I can forgo the bonus items if they're tricky.
Thanks in advance!
SDL
