getting the comment with =

[Janice Lee via OfficeKB.com]

Hi,
I have multiple sheets for my spreadsheet, and some cells refer are self-
referencial of cells on another sheet.
I want to know whether it's possible to also get the comments with this =
sign. As is, it's only getting the cell value and not the comment.
Thank you!

 

[Guest]

Use copy>paste special>all on the cell with the comment. This will bring
the comment across
Pat

 

[Janice Lee via OfficeKB.com]

i actually wanted to use equal sign, so that whenever i update a cell, the
cell that's self-referencial is automatically updated.
if i copy > pastespecial, then i would have to do that every time i update
the value of the cell.
is there a way to get the comment with = sign?

 

[Gord Dibben]

Janice

You cannot copy the comment just by linking the cell to another.

A workaround of sorts.

Copy a cell with a Comment then switch to other sheet and Paste Special>Paste
Link.

Then Paste Special>Comments.

The cell-linking will remain in effect.

If you change the original Comment you will have to re-copy it.

BTW.....I think that "self-referential" is not the proper syntax.

Cells that reference themselves cause "Circular Reference" errors.

What you are doing is referencing or linking a cell to another cell.


Gord Dibben Excel MVP

 

[Dave Peterson]

Saved from a previous post:

Is a UserDefinedFunction ok?

Option Explicit
Function EchoComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
EchoComment = ""
Else
EchoComment = FCell.Value
End If

End Function

You'd use it like this:

=echocomment(a1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

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

 

[Gord Dibben]

Way cool!

Gord

Saved from a previous post:

Is a UserDefinedFunction ok?

Option Explicit
Function EchoComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
EchoComment = ""
Else
EchoComment = FCell.Value
End If

End Function

You'd use it like this:

=echocomment(a1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

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

 

[Yvonne_G]

Dave,

I copied the UDF to the sheet module and put values and comments in A1 and
some other cells.
When I put in F1 this =echocomment(A1) it results in #NAME? Same for all
other positions.
Why? What did I do wrong?

Jack Sons
The Netherlands

 

[Dave Peterson]

Don't put it in the sheet module. Move it to a General module.

Dave,

I copied the UDF to the sheet module and put values and comments in A1 and
some other cells.
When I put in F1 this =echocomment(A1) it results in #NAME? Same for all
other positions.
Why? What did I do wrong?

Jack Sons
The Netherlands