Formula based values in comment

[Bram1212]

Hi!

I was wondering if it is possible to show the result of a formula in the comment section. Based on what product ID is showed in B4 I would like to show the result of an VLOOKUP function in the comment section of C4.

Thanking you in advance for any tips!

 

[Bram1212]

Any tips on this issue?

 

[Yongle]

You need to use VBA
Which version of Excel are you using ?


Latest versions of Excel contain BOTH "Notes" AND "Comments" - they are different things

Microsoft has muddied the water
- "Notes" is a new name for the OLD "Comments"
- "Comments" are a NEW feature

see https://support.office.com/en-us/ar...in-excel-bdcc9f5d-38e2-45b4-9a92-0b2b5c7bf6f8

 

[Bram1212]

I am using the 2016 version. Looking for a solution in the 'notes' section.

Any idea on what VBA I should use? Got no clue where to start even.

Thanks for reaching out.

 

[Yongle]

Excel object = "Note" ... VBA object = "Comment" - thank you Microsoft

Place code in sheet module of comment sheet
- right click sheet tab \ View Code \ paste into code window \ back to Excel with {ALT}{F11}
- save workbook as macro enabled

Let me know if you need help modifying code to apply to more than one cell

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, aComment As String
    With Target
        If .Address(0, 0) = "B4" Then
            Set Rng = Sheets("Other Sheet").Range("A:C")
            aComment = WorksheetFunction.VLookup(Target, Rng, 2, 0)
            On Error Resume Next
            With .Offset(, 1)
                .ClearComments
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=aComment
            End With
        End If
    End With
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]ID-005[/td][td=bgcolor:#FFC5E2]the "Note" in cell C4 is amended whenever B4 changes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Comment Sheet[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Product ID[/td][td]Description[/td][td]Type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]ID-001[/td][td]Cat[/td][td]Feline[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]ID-002[/td][td]Dog[/td][td]Canine[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]ID-003[/td][td]Mouse[/td][td]Rodent[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]ID-004[/td][td]Lion[/td][td]Feline[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]ID-005[/td][td]Tiger[/td][td]Feline[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Other Sheet[/td][/tr][/table]

 

[Bram1212]

Hi Yongle,

Thanks for this solution. It seems to work!

I would like to use this function for a longer list of product ID's in column B. How would I amend the code to apply it on other values in B4, B5, B6, B7 etc and show the vlookup value in the note in C4, C5, C6, C7 etc.

 

[Yongle]

try this ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, aComment As String
        If Not Intersect(Target, Range("B4").Resize(Rows.Count - 3)) Is Nothing Then
            Set Rng = Sheets("Other Sheet").Range("A:C")
            aComment = WorksheetFunction.VLookup(Target, Rng, 2, 0)
            On Error Resume Next
            With Target.Offset(, 1)
                .ClearComments
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:=aComment
            End With
        End If
End Sub

 

[Bram1212]

It seems to work in my small test sheet. I will apply it to the larger sheet later today.

Many thanks for your help!

 

[Bram1212]

Hi Yongle,

It works, this is exactly what I was looking for, awesome!

Not sure if I should open a new thread for this, but do you also know a workaround for the red triangles in the cells with a note? Would like to not have them in the sheet since the make the view very chaotic. The standard 'hide note' function in excels makes them go away but also hides the note itself.

Many thanks if you are able to help again.

 

[Yongle]

Suggest you start a separate thread.