How to extract cell comment if it matches range and criteria

[hma]

I need to make a formula in excel that will bring back a specific cells "comment" only if the criteria matches. So if "A:A, matches A3, then bring back the comment in A4". Any help would be very appreciated. Thanks

 

[Hui]

Hma

Firstly, welcome to the Chandoo.org Forums

When you say is A:A matches A3 this is a bit confusing as A3 is part of A:A and will obviously match, but there could be 1,000,000 other values in A:A that don't match ?

Can you post a sample file with a better explanation?

 

[hma]

Hma

Firstly, welcome to the Chandoo.org Forums

When you say is A:A matches A3 this is a bit confusing as A3 is part of A:A and will obviously match, but there could be 1,000,000 other values in A:A that don't match ?

Can you post a sample file with a better explanation?

Sorry I did not explain myself very well.

ok so there is data in the data tab in the attached excel spreadsheet. It has names in "a" and comments in "b". On the tab called "rollup" I want to bring back the comment from tab "data" "b" only if it "data" "a" matches what is in tab "rollup" "a". Bringing the comment back to "rollup" "b"

 

[hma]

Sorry I did not explain myself very well.

ok so there is data in the data tab in the attached excel spreadsheet. It has names in "a" and comments in "b". On the tab called "rollup" I want to bring back the comment from tab "data" "b" only if it "data" "a" matches what is in tab "rollup" "a". Bringing the comment back to "rollup" "b"

Sorry I should mention also that I need it to be like an if statement so "if" what is in "roll up" "a" matches anything in "data" "A:A" then it brings back the comment.

 

[Hui]

Try the following UDF

Function Extract_Comment(myCell As Range) As Variant

Extract_Comment = ""
With Worksheets("Data").Range("A:A")
  Set c = .Find(myCell, LookIn:=xlValues)
  If Not c Is Nothing Then
  On Err GoTo 0
  Extract_Comment = c.Offset(0, 1).Comment.Text
 
  End If
End With
Exit Function

0:
Extract_Comment = "Not found"

End Function

Copy and paste the above in a code module in VBA

To use in Rollup!b1 simply enter: =Extract_Comment(A1)
Copy down

see attached file:

 

[hma]

Try the following UDF

Function Extract_Comment(myCell As Range) As Variant
 
Extract_Comment = ""
With Worksheets("Data").Range("A:A")
  Set c = .Find(myCell, LookIn:=xlValues)
  If Not c Is Nothing Then
  On Err GoTo 0
  Extract_Comment = c.Offset(0, 1).Comment.Text
 
  End If
End With
Exit Function
 
0:
Extract_Comment = "Not found"
 
End Function

Copy and paste the above in a code module in VBA

To use in Rollup!b1 simply enter: =Extract_Comment(A1)
Copy down

see attached file:

It is not working. giving a compile error: Invalid outside procedure.

 

[Hui]

Your going to have to be more specific about what isn't working as it clearly works for me:


How isn't it working?
What are you doing?
is it the same file or have you copied or modified it ?