Including comments in referenced cell

Topic

New Reply

Can I get a referenced cell to include its comment?
If A1 worksheet 1 has a comment and I want to reference it in A1 worksheet 2 including its comment.

Reply | Quote

Replies

Skipro,

Here is a UDF that you can call from any cell and reference another. The comment as well as the value will be copied. In this example, Sheet2 Cell A1 pulls the value and copies the comment from Sheet1 Cell A1

Sheet2 Cell A1 =CopyComment(Sheet1!A1)

Code:

Public Function Comment2Comment(Rng As Range) As Variant
    msg = Rng.Comment.Text
    ActiveCell.ClearComments
    ActiveCell.AddComment
    ActiveCell.Comment.Text Text:=msg
    Comment2Comment = Rng.Value
End Function

There is one cravat: Updating the cell value on Sheet 1 Cell A1 will update the value in the cell with the call but the comment if edited will not update. Refreshing the macro in the calling cell will update the comment.

HTH,
Maud

Reply | Quote

Another caveat:
This is being called as a worksheet function, not a VBA function. The active cell may not allows be the cell with the formula, so the comment may be created in a different cell than anticipated and it tends not to update.

This may be a better approach. It will update when a calculation is done and it will update the comment on the cell that calls the function, not the active cell. This will work better if you use the function in more than 1 cell in the workbook.

Code:

Public Function Comment2Comment(Rng As Range) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  sMsg = Rng.Comment.Text
    With .Caller
      .ClearComments
      .AddComment
      .Comment.Text Text:=sMsg
    End With
  End With
  Comment2Comment = Rng.Value
End Function

Steve

Reply | Quote

Maud & Steve,
Thank you.

Maud,
Did you mean, “Sheet2 Cell A1 =Comment2Comment(Sheet1!A1)”, not “Sheet2 Cell A1 =CopyComment(Sheet1!A1)”

Reply | Quote

Maud means Comment2Comment, that is the name of the function.

Steve

Reply | Quote

Steve,
When I use your function but do not have a comment in 1st cell, I get #Value!. Add a comment, OK. Delete comment get #Value! but does not remove the comment in cell 2.

Reply | Quote

Yes Skipro, that is what I meant. The formula in Sheet2 Cell A1 is =Comment2Comment(Sheet1!A1)

Reply | Quote

Yes the presumption of the UDF is that the range called will have a comment, and there will be an error if not. This correction checks for an error. Also if there is no comment the comment is cleared in the caller cell.

Code:

Option Explicit
Public Function Comment2Comment(Rng As Range) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
    With .Caller
      .ClearComments
      If sMsg  "" Then
        .AddComment
        .Comment.Text Text:=sMsg
      End If
    End With
  End With
  Comment2Comment = Rng.Value
End Function

Steve

Reply | Quote

Steve,
Thanks.

Reply | Quote

You are very welcome.

Steve

Reply | Quote

How can I copy the comment without the contents?

Reply | Quote

I don’t know what you mean exactly. Are you looking for a formula, macro, manual and what exactly do you want to do?

Steve

Reply | Quote

Steve,
Thank you for your continuing help.

My original request was to copy cell [value] and comments.

But I have 2 worksheets that have a few cells that are related but with different formulas and values. I need to carry the information from the source cell “comment” over to the destination cell in a similar comment [copy comment], but not the formula or value as I need to maintain the destination cells formulas/values. This would be the same as the original request without including the source cell value or formula, just the comment.

Reply | Quote

Something like this perhaps? The cell contents will be the comment from the reference.

Code:

Option Explicit
Public Function GetComment(Rng As Range) As Variant
  Dim sMsg As String
  Application.Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
  GetComment = sMsg
End Function

If a comment is edited, a recalc will be needed to update.
If there is no comment then the function will return a null string.

Steve

Reply | Quote

Steve,
Thanks, but not what I need.
A1[worksheet1] has a value of 5 and a comment. – I refer to this cell as the source cell.
B2[worksheet2] has a value of 9[this may be a simple value or an output of a function or formula]. I refer to this cell as the destination cell. In this instance, for the comment only, not the value/formula.
I need the comment from A1[worksheet1] to be “copied”? or added to B2[worksheet2] without affecting the value of B2[worksheet2].
In the original solution, the value along with the comment of the source cell was copied to the destination cell, overriding the value of the destination cell. This works great when I need a “copy” of the cell, but in this instance, I need only the comment inserted/copied without overriding the value.
Your last suggestion overrode the value of the destination cell with the contents of the comment.
Hope this is clearer.

Reply | Quote

Here is a simple macro to do what you want.

Code:

Option Explicit
Option Explicit
Sub GetComment()
  Dim rSource As Range
  Dim rDest As Range
  
  'change as desired
  Set rSource = Range("Worksheet1!A1")
  Set rDest = Range("Worksheet2!B2")
  
  rSource.Copy
  rDest.PasteSpecial xlPasteComments
End Sub

It creates a comment in Worksheet2!B2 that has the same value of the comment in Worksheet1!A1, but does not affect the value in B2. It is not very general, but you have given us no infomation on how to genearalize it.

Steve

Reply | Quote

Here is an idea for a UDF. You give it both the source and destination cell.

Code:

Option Explicit
Function GetComment(rSource As Range, rDest As Range)
  Dim sMsg As String
 
  On Error Resume Next
  sMsg = rSource.Comment.Text
  On Error GoTo 0
  
  With rDest
    .ClearComments
    If sMsg  "" Then
      .AddComment
      .Comment.Text Text:=sMsg
    End If
  End With
  GetComment = True
End Function

This formula does NOT go in the destination cell, it goes in a cell that is out of the way as it just returns a TRUE if there are no errors.

Steve

Reply | Quote

I don’t see how you could do this with a function in the cell. You could do it with a macro, but I am not sure how you are thinking of calling it.

a function can be created (some are on this page)
1) Return some value (could be from another cell or group of cells). This is the normal use
2) return a value in the cell and add a comment

I can create a function to get the comment from the source and put it into the destination comment, but adding this formula will remove the value in the cell. Now if that value is in a different source (source2) or the result of some function that can be replicated in the UDF, the destination can get essentially the same value. But that function would have to contain the function to get the value and the function to get the comment.

In other words, when you add the function B2[worksheet2]
=GetComment(Worksheet1!A1)

It is easy to get the comment, but how does the function know to return the value of 9?

A simple way is:

Code:

Option Explicit
Public Function GetComment(Rng As Range) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
    With .Caller
      .ClearComments
      If sMsg  "" Then
        .AddComment
        .Comment.Text Text:=sMsg
      End If
    End With
  End With
  GetComment = 9
End Function

But I expect you may want something more general…

[From your description you do not seem to want a UDF since by its very nature, you will be replacing the destination formula/value with the new formula. If you want a macro, what is it exactly what you want to do with it? It could be as simple as creating a table of source and destinations, then having the macro read the table and create comments in each of the destination cells based on the source cell referenced next to it in the table.]
Steve

Reply | Quote

Steve,
I have been unavailable to follow-up.

The content of the destination cell varies too much to have a script/macro/UDF specific for each, that is why I have not/cannot answer your request for specifics.
Sometimes it is simply a “Sum” of cells in that worksheet2 but needs the comment from a cell in worksheet 1 to be included but whose cell has a different value.

What I need is something to carry the comment to a cell in a different worksheet as in “GetComment” and “Comment2Comment”, but THEN allows another function specific for that cell to run for that cell to formulate it’s intended value.

Ex:
Sheet1!A1 =1 with comment
Sheet2!B1 = Sum(A1:A3) from Sheet2! but to include comment from Sheet1!A1
Sheet2- A1=1, A2=1, A3=1
therefore B1=3 and includes comment Sheet1!A1
I thought formulas ran from left to right. If so, the thought is to =GetComment(sheet1!A1) [this would 1st get comment]
and then Sum(A1:A3) which would then give intended value to B1
This would allow B1 to get the comment AND THEN add the value of the “SUM” thereby having essentially 2 results.
Is there a priority or order concept that would work?

If so, then it may not matter if the GetComment part carried a value if this value would be overwritten by the second part of the formula.

Reply | Quote

Have you tried my last response (#18)
You would enter in Sheet2!B1 the formula = Sum(A1:A3)
Then in some cell (it does not matter one that is not doing anyting) you would enter:
=GetComment(Sheet1!A1,Sheet2!B1)

and the comment would be transferred.

Alternately you could use a modification of the version from #16, but just return a value of zero

Code:

Option Explicit
Public Function GetComment(Rng As Range) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
    With .Caller
      .ClearComments
      If sMsg  "" Then
        .AddComment
        .Comment.Text Text:=sMsg
      End If
    End With
  End With
  GetComment = 0
End Function

And then in Sheet2!B1
=GetComment(Sheet1!A1) + sum(A1:A3)

to add the real formula onto the returned value of zero.

Steve

Reply | Quote

Steve,
Ah, ha!
That seems to be it. Your solution is also where my mind was going so I could cover all circumstances, essentially a 2 step process.
1-Get Comment
2-get value
I was trying =GetComment(Sheet1!A1,Sum(A1:A3) and would not work, wrong syntax. I did not know how to get the second half incorporated. I was not aware of the “+” syntax. Shows, “close” in spreadsheets is not good enough. Your response correcting my idea and was what I was looking for.
Hopefully when I integrate this into my work, and future work, it will work as it appears to do.

If the value is derived from another UDF would the formula be: =GetComment + “name of 2nd UDF”

Is order critical?

Again, Thanks!

Reply | Quote

The “+” sysntax is addition. It is no different than doing
=Sum(B1:B3) +Sum(A1:A3)

to add 2 values together. In this case the first UDF always has a value of zero.

I don’t see why the order would matter unless the values are dependent on one another. The left formula would go first then the one on the right, so it would depend on what the 2 functions do exactly.

Steve

Reply | Quote

Steve,
It seems the “+” is the basic arithmetic function. Can I add a non-arithmetic function after GetComment so it will run and give a return? Such as an IF or VLookup function?

=GetComment then IF(Z3=”xxx”,”yyy”).

Reply | Quote

SkiPro,

What Steve is trying to say is use: [noparse]=GetComment(D1)+D1[/noparse]
as the forumla to return the value and comment from cell D1.
36145-SteveGetComment
GetComment returns a 0 value so the formula becomes 0+D1 which of course does not alter the value of D1.
HTH :cheers:

BTW: Nice code Steve!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

If your function returns text, then you need to use the ampersand(&)
=GetComment(Sheet1!A1) & IF(Z3=”xxx”,”yyy”)

But this will yield a 0 followed by the text.

do you need the ability to have a number for some and also text for others? Then you need to tell the function what you want to return. Something like:

Code:

Option Explicit
Public Function GetComment(Rng As Range, sType As String) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
    With .Caller
      .ClearComments
      If sMsg  "" Then
        .AddComment
        .Comment.Text Text:=sMsg
      End If
    End With
  End With
  Select Case UCase(sType)
    Case Is = "N"
      GetComment = 0
    Case Is = "T"
      GetComment = ""
    Case Else
      GetComment = CVErr(xlValue)
  End Select
End Function

And Call like:
for returning numbers:
=GetComment(Sheet1!A1, “n”) + sum(A1:A3)

For returning text:
=GetComment(Sheet1!A1, “t”) & IF(Z3=”xxx”,”yyy”)

Steve

Reply | Quote

What can I do if the call may return a number or a string?

Reply | Quote

It is not the call you need to worry about it is the formula you are attaching. Pick the techniquye based on the results of the formula (will it be text or a number).

If it could be either, you can test the results:

=if(isnumber(IF(Z3=”xxx”,Z4,Z5)),GetComment(Sheet1!A1, “n”) + IF(Z3=”xxx”,Z4,Z5),GetComment(Sheet1!A1, “t”) & IF(Z3=”xxx”,Z4,Z5))

If the result of the formula is a number, add the formula result to a zero, otherwise concatenate the null string.

Steve

Reply | Quote

Steve,
To clarify, the return is not predictable and may be either a number or a text. Both the result’s numbers or texts will vary, depending on the conditions. The value of the cell with the comment to be copied, may be a text or a number and they also will vary.
There is no single text or number I can test.
I do not follow your last reply.
To simplify and clarify I have attached a simple example which I think includes most of the variables I need.
F2:F5 is results of A:C, these returns must be left intact. I need to add the comment from H2, whose value is a text, to these cells.
Another series F7:F10 [same as F2:F5] need the comment from H7, whose value is a number, added to these cells.

A reply to this example will probably be easier for me to follow.

Reply | Quote

An option to not enter the current formula 3 times would be to feed it to the UDF. But this would require modifications to the formula (each dbl-quote would have to be doubled, and the formula would be “locked” so copying it would not change it, it would have to be done manually.

For example In F2 you could use:
=getcomment(H2,”=IF(A2=””””,””no entry””,IF(A2=””PRV””,””PRV””,B2+B3))”)

In F3:
=getcomment(H2,”=IF(A3=””””,””no entry””,IF(A3=””PRV””,””PRV””,B3+B4))”)

Etc

If the UDF was:

Code:

Option Explicit
Public Function GetComment(Rng As Range, sFormula) As Variant
  Dim sMsg As String
  With Application
    .Volatile
  On Error Resume Next
  sMsg = Rng.Comment.Text
  On Error GoTo 0
    With .Caller
      .ClearComments
      If sMsg  "" Then
        .AddComment
        .Comment.Text Text:=sMsg
      End If
    End With
  End With
  GetComment = Evaluate(sFormula)
End Function

I find the version with 3 entries better since it allows one to use the formula directly and excel’s formula entry, relative copying etc, can all be used, so would not recommend this version, but it is an option.

Steve

Reply | Quote

It has nothing to do about the cell you are getting the comment from. It has only to do with the the results of the formula you want to report in the cell.

in your example. In cell F2:

Code:

=IF(ISNUMBER(IF(A2="","no entry",IF(A2="PRV","PRV",B2+B3))),getcomment($H$2,"n")+IF(A2="","no entry",IF(A2="PRV","PRV",B2+B3)),getcomment($H$2,"t")&IF(A2="","no entry",IF(A2="PRV","PRV",B2+B3)))

Copy from F3:F5 (these get comment H2)

In cell F7:

Code:

=IF(ISNUMBER(IF(A7="","no entry",IF(A7="PRV","PRV",B7+B8))),getcomment($H$7,"n")+IF(A7="","no entry",IF(A7="PRV","PRV",B7+B8)),getcomment($H$7,"t")&IF(A7="","no entry",IF(A7="PRV","PRV",B7+B8)))

Copy from F8:F10 (these get comment H7)

If you start with a formula in the cell of
=CurrentFormula

And the comment is in Cell A1

Then you need to change the formula in the cell to:
=if(isnumber(currentformula),getcomment(A1,”n”)+currentformula,getcomment(A1,”t”)&currentformula)

What the formula is doing is checking to see if the currentformula is a number. If it is, get the comment and give the number zero as the result so it can be added to the currentformula’s result (giving the currentformula’s value. If it is not a number, then get the comment and return a result of a null string and concatenate it to the currentformula again leaving the value of the currentformula.

Whether the cell with the comment has a value of a number or text is irrelevant as you are not doing anyting with that value.

Steve

Reply | Quote