Can a formula find a comment in a cell

[Guest]

I would like to check to see if a cell has a comment or not, without using a
macro. In other words, If the value of the cell is between 10 and 20, and
there is not a comment attached to that cell, I want to display "No". If the
value of the cell is between 10 and 20, and there is a comment attached to
that cell, I want to display "Yes".

In a macro, I can use:
If (Cells(row, col).Comment Is Nothing) Then
'do nothing, there is no comment to process
Else
' We have a comment ... do all the stuff I need to do

But since I only want to display some text, nothing complicated, I'd really
like to use an "IF" function. Can this be done?

Thanks
CRH

 

[Arvi Laanemets]

Hi

Create an UDF.
An example:

Public Function GetComment(parRange As Range, Optional parTime As Date) As
String

If parRange.Count = 1 Then
GetComment = parRange.Comment.Text
Else
' When several cells were passed as parameter, nothing is returned
End If
End Function

Now you can get the comment, or check for it's existence - as you want.
=GETCOMMENT("A1")
or
=GETCOMMENT("A1",NOW())
or
=NOT(ISERROR(GETCOMMENT("A1")))

 

[Guest]

Thank you, Arvi, that worked very well.

Hi

Create an UDF.
An example:

Public Function GetComment(parRange As Range, Optional parTime As Date) As
String

If parRange.Count = 1 Then
GetComment = parRange.Comment.Text
Else
' When several cells were passed as parameter, nothing is returned
End If
End Function

Now you can get the comment, or check for it's existence - as you want.
=GETCOMMENT("A1")
or
=GETCOMMENT("A1",NOW())
or
=NOT(ISERROR(GETCOMMENT("A1")))

 

[bplumhoff]

Hello,

Arvi already told you how to achieve this with a UDF.

A "formula-only" approach:
http://www.sulprobil.com/html/get_cell.html

HTH,
Bernd