ExcelBanter - Referencing Comments in Excel 2003

Hi Alan

There is no built-in functionality to do that. You can try the below UDF
(User Defined function).

Syntax:
=VLOOKUP_COMMENT(strLookupValue,rngLookUpArray,int Column)

rngLookUpArray is the Lookuprange
strLookupValue is the lookup string or cell reference
intColumn is the column to be concatenated

Examples:
'1. To vlookup 1 in col A and return value from Column C. If comments are
present for Col C the function will return the comments to the formula cell.

=VLOOKUP_COMMENT(1,A:C,3)

OR

'with the lookup value in cell D1
=VLOOKUP_COMMENT(D1,A:C,3)

Below is the code to be pasted to the code module. From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the formula.

Function VLOOKUP_COMMENT(strLookupValue As String, _
rngLookUpArray As Range, intColumn As Integer)
'Jacob Skaria
Dim lngRow As Long, rngTemp As Range
For lngRow = 1 To rngLookUpArray.Rows.Count
If CStr(rngLookUpArray(lngRow, 1)) = strLookupValue Then _
VLOOKUP_COMMENT = rngLookUpArray(lngRow, intColumn): Exit For
Next

Set rngTemp = Application.Caller
If Not rngTemp.Comment Is Nothing Then rngTemp.Comment.Delete
If Not rngLookUpArray(lngRow, intColumn).Comment Is Nothing Then
rngTemp.AddComment rngLookUpArray(lngRow, intColumn).Comment.Text
End If
End Function

If this post helps click Yes
---------------
Jacob Skaria

"Alan" wrote:

Hi,

I am trying to populate some comments with references to another document,
is it possible to do a lookup style function in order to do this?

Cheers