Formula for pulling comments from one sheet to another

[gak67]

Hi Experts

Is there a formula or function that can be used to pull not just the value/contents of a cell in another sheet, but also any comments that have been attached to that cell? Obviously I could just copy the comments from that cell, but the cell referenced can vary, so sometimes it will have a comment and other times it won't.

Thanks in advance for your help.

[JieJenn]

To extract comments, you might need a macro to do that. Maybe this might help.

Sub ExtractComment()
Dim cmt As Comment
Dim ws As Worksheet, wsComment As Worksheet
Dim l As Long

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set wsComment = ThisWorkbook.Worksheets.Add

l = 1
With ws
    For Each cmt In .Comments
        wsComment.Cells(l, 1).Value = cmt.Text
        l = l + 1
    Next cmt
End With

Set ws = Nothing
Set wsComment = Nothing
End Sub

[Jpenny87]

No other way besides macros?