use cell value to "lookup" info to create comment?

[auntie bella]

Hi Guys,

Is it possible to create/update comments using the cell value as a kind of lookup?

To explain...
On sheet1 column N - I have some numbers eg. 170 143 161 etc. - each one will appear several times in the column.

In sheet2 I have a list of names in each group i.e.
170 143 161
joe carol dave
fred ann bill
tom richard harry

when someone hovers over (or clicks into) the cell containing 170 I want them to see Joe fred tom in the comment box.

Then if i add names to sheet2 i want it to update.

Is this at all possible - if so can you give me a starting point? (I am still a novice at VBA but can often cobble something together from other peoples posts and answers but couldn't find anything around this.)

Any help would be much appreciated.

thankyou

 

[Trevor G]

Take a look at this code as it will place a comment in a cell and fill with data from another cell. Check out this link to help you

http://www.contextures.com/xlcomments03.html#Plain

Sub CommentAddOrEdit()
'adds new plain text comment or adds text
'at end of existing comment text
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
Set cmt = Range("B3").AddComment
cmt.Text Text:=Range("B4").Value


End If

'type to add comment text to selected shape
cmt.Visible = True
cmt.Shape.Select

End Sub

 

[auntie bella]

ooh!

Many thanks Trevor.

I'm getting something...
this works for my active cell and is creating a comment box (nothing in it yet - even though i think it should have.)
I will play around with the start you have given me (best way to learn) and come back (when I am truly stuck)

thanks
again.