show Comment using INDEX

[Lucy]

Hi there,

A B
1 Apple 44 (with red-arrow comment "fruit")
2 Pine 55
3 Apple 66
4 Orange 77

I've added comment "fruit" on cell B1
After I use INDEX function to locate B1, only the value in the cell shows
but NOT
the comments. Which means, only "44" shows.

How can I show the red-arrow comment "fruit" as well?

Thanks.

 

[Gary''s Student]

The INDEX function can retrun the value of a cell. It cannot return the
associated comment in the cell or the format of that cell.

 

[Lucy]

I'm looking for creating a user defined function. Will macros help?

 

[ryguy7272]

There is a comprehensive list of comments-related stuff here:
http://www.contextures.com/xlcomments01.html#All


Regards,
Ryan--

 

[Gary''s Student]

Let's assume that by using some combination of MATCH / INDEX / etc. we can
get the Address of the cell. So then some cell, say Z100 displays B1 as a
result of the worksheet formula.

=INDIRECT(Z100) will then display the Value of B1
=coment(Z100) will then display the comment in B1.

The UDF is:

Function coment(s As String) As String
coment = ""
If Range(s).Comment Is Nothing Then Exit Function
coment = Range(s).Comment.Text
End Function

 

[Lucy]

Thanks Gary, but this UDF shows an error message & not working.
Btw, is this UDF returns the comment as "text in cell" or "comment"?
Because I hope it can return to destination cell as "comment" with the text
inside the cell.

If I use this UDF in cell C1 = coment (B1)
it will look exactly same as B1 --> 44 (with red-arrow comment "fruit")

 

[Gary''s Student]

My mistake. I was assuming that you didn't the have address, but were
getting it via some sort of lookup function. If you want to get the comment
in cell B1 directly, then put:

=coment(B1) in another cell and change the code to:

Function coment(r As Range) As String
coment = ""
If r.Comment Is Nothing Then Exit Function
coment = r.Comment.Text
End Function

REMEMBER: remove the old verison of the UDF before pasting in the new
version.

 

[Lucy]

Hi Gary,

This one works, thanks.

But I think I didn't explain myself clearly. (it's hard to explain here
without graph/picture, sorry!)
I do not want to show the comment in the cell. But I want to show it as a
"comment".

A B
1 Apple 44 (with red-arrow comment "fruit")
2 Pine 55
3 Apple 66
4 Orange 77

in cell C1, I put =INDEX(A1:B4,1,2)
so result of C1 will be "44"

Since cell B1 has a comment attached, I want to bring this comment to C1 as
well. The UDF you've provided will overwrite "44".
The ideal result I'm looking for is "44" still shows in C1 and there will be
red-arrow at the corner as a comment.

I have done similar VBA before with Vlookup but unable to modify the code to
work as INDEX.

Again, really appreciate your help!

 

[Dave Peterson]

Maybe this will help you get started:

Option Explicit
'=index(array,row_num,column_num)
Function IndexComment(myRng As Range, _
Optional myRow As Long = 1, _
Optional myCol As Long = 1) As Variant

Dim res As Variant

Application.Volatile True

res = Application.Index(myRng, myRow, myCol)

IndexComment = res

With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If

If myRng(myRow, myCol).Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myRng(myRow, myCol).Comment.Text
End If
End With

End Function

This kind of function could be one calculation behind. If the comment in the
table changes, then you'll want to force a recalculation before you believe the
results.

Application.volatile true
means that excel will recalculate each of these formulas each time excel
recalculates. You may notice a slowdown in your workbook.

If you remove this line, then the results in the cell will be ok, but the
comment may be wrong.

(The results in the cell should always be ok--it's the comment that's the
trouble.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=indexcomment(a1:c20,3,2)