I would like to be able to reference both the contents of another cell
and the comments from that cell too. I want comments to be generated
where comments exist in the cell being referenced. I have
idadvertantly managed to get the comments from the donor cell to be
returned as values in the formula cell - but I want them to apear as
comments.

I have found some interesting stuff at David McRitchie's page
http://www.mvps.org/dmcritchie/excel/ccomment.htm but I don't think it
does what I need.

Any help would be appreciated. I am using '97.

Many thanks

Unfortunately I can't follow what you want, but you can test the
existence of a comment with HasComment user defined function.
Do a recalculate Ctrl+Shift+F9 first if you don't have volatile on your functions.

If you want a comment or other text in a cell

C: =IF(AND(personal.xls!HasComment(A1),personal.xls!H asComment(B1), True, False)

D2: =IF(AND(personal.xls!HasComment(A1),personal.xls!H asComment(B1),
personal.xls!MyComment(A1), "(use secondary Comment)")
E2: =personal.xlsMyComment(B1)

If you are trying to add a cell comment or delete a cell comment you will need
to use a macro such as are on my page that you referenced
http://www.mvps.org/dmcritchie/excel/ccomment.htm with some
changes in the code. I suspect that it code that you want, but would need
simple clear instructions that I can understand as to which columns are to
be tested for what and which comments are to be added from where, or
which comments are to be deleted with column numbers.

If you are not familiar with user defined functions or macros see the
color box at the top of the page ccomment.htm which points you to
http://www.mvps.org/dmcritchie/excel....htm#havemacro
since you have the macro (or function) from this page #havemacro is the
most important topic for you.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"RJS" wrote in message ups.com...
I would like to be able to reference both the contents of another cell
and the comments from that cell too. I want comments to be generated
where comments exist in the cell being referenced. I have
idadvertantly managed to get the comments from the donor cell to be
returned as values in the formula cell - but I want them to apear as
comments.

I have found some interesting stuff at David McRitchie's page
http://www.mvps.org/dmcritchie/excel/ccomment.htm but I don't think it
does what I need.

Any help would be appreciated. I am using '97.

Many thanks

Thanks - to clarify what I need to do:

Say, for simplicity, I have a set of values in 10 cells (A1:A10) in
sheet1 of a workbook which
also has comments in each cell. If I have another worksheet
(worksheet2) and want to replicate this column of values, I just enter
"=Sheet1!A1" etc. But this does not also return the comments.

I want references to return both the values and the comments (as
comments).

My real spreadsheet is a larger one, hence why I want this to be
automatic.

I am a VB novice although follow the basics so I could follow some
clear guidance - I think!

Thanks

A macro will do what it is told, I doubt that you want a macro that
only works between two specific sheets and with only one range
if that was all you wanted you could use Copy, then Edit, Paste special, comments

Okay I see, you want a formula to copy the comments, and
you cannot do that, because a formula can only obtain a value
it cannot format any cell including itself.

You could conceivably have the macro look at the formula and copy the comments
if it is a simple assignment, but that would be unreliable, because
you would have to run the macro.

Can you work with the original page and use filtering.

Or copy the entire page and delete rows you do not want.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"RJS" wrote in message oups.com...
Thanks - to clarify what I need to do:

Say, for simplicity, I have a set of values in 10 cells (A1:A10) in
sheet1 of a workbook which
also has comments in each cell. If I have another worksheet
(worksheet2) and want to replicate this column of values, I just enter
"=Sheet1!A1" etc. But this does not also return the comments.

I want references to return both the values and the comments (as
comments).

My real spreadsheet is a larger one, hence why I want this to be
automatic.

I am a VB novice although follow the basics so I could follow some
clear guidance - I think!

Thanks