Forum

Formula Results in ...
 
Notifications
Clear all

Formula Results in Comments box

6 Posts
3 Users
0 Reactions
95 Views
(@littlerumbling)
Posts: 3
Active Member
Topic starter
 

Hi, Is there a way of dynamically displaying the reults of a formula in a comments/text box?  In the attached spreadsheet, I have a table of people and their test assessment levels in the two adjacent columns.  The range H4 to J6 is a count of the numbers of people at an intersection between the result they achieved in test 1 and test 2.  E.g. cell H4 is the number of people who were "Above" in both tests and J5 are the names of those who "Attained" in the first test and were "Below" in the second.  In the comments boxes are the names of those individuals which I would like to change dynamically as and when the test results change .  I want to be able to have the same functionality in all the yellow highlighted cells.  Thanks for your help and please let me know if you would like any further information.

Cheers,

Mark

 
Posted : 28/01/2018 1:53 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Mark

I don't think you can display the result of a formula in a Comment. You will need VBA for that.

What you request is complex and difficult to do as you need to (1) get the required names and then (2) put the result into the comments dynamically.

The best I can come up with is the attached. The range is hard-coded and it uses a worksheet change event.

Any changes in the Result range will trigger the update. Not sure if it will work for other scenarios.

Good luck.

Sunny

 
Posted : 30/01/2018 12:44 am
(@littlerumbling)
Posts: 3
Active Member
Topic starter
 

Thanks, Sunny.  I can certainly work with this.  If anybody out there has a VBA solution.  I would be very interested to see it. 

Many thanks

 

Mark

 
Posted : 30/01/2018 1:57 pm
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Mark

My solution is in VBA.

If you don't want to use the Worksheet Change event, you can insert a button on the worksheet and attach my macro to it.

Sunny

 
Posted : 30/01/2018 6:58 pm
(@littlerumbling)
Posts: 3
Active Member
Topic starter
 

Hi Sunny,

So it is.  My mistake and thanks once again for your help.

 

All the best,

 

Mark

 
Posted : 31/01/2018 10:05 am
 Madi
(@madisal66)
Posts: 61
Trusted Member
 

you amazing SunnySmile... today i learn one more new option.

Thanks to all online training hub & Lynda

 
Posted : 01/02/2018 3:22 am
Share: