software

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
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 thanksUnfortunately 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), quot;(use secondary Comment)quot;)
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
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
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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;RJSquot; gt; wrote in message ups.com...
gt; I would like to be able to reference both the contents of another cell
gt; and the comments from that cell too. I want comments to be generated
gt; where comments exist in the cell being referenced. I have
gt; idadvertantly managed to get the comments from the donor cell to be
gt; returned as values in the formula cell - but I want them to apear as
gt; comments.
gt;
gt; I have found some interesting stuff at David McRitchie's page
gt; www.mvps.org/dmcritchie/excel/ccomment.htm but I don't think it
gt; does what I need.
gt;
gt; Any help would be appreciated. I am using '97.
gt;
gt; Many thanks
gt;
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
quot;=Sheet1!A1quot; 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!

ThanksA 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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;RJSquot; gt; wrote in message oups.com...
gt; Thanks - to clarify what I need to do:
gt;
gt; Say, for simplicity, I have a set of values in 10 cells (A1:A10) in
gt; sheet1 of a workbook which
gt; also has comments in each cell. If I have another worksheet
gt; (worksheet2) and want to replicate this column of values, I just enter
gt; quot;=Sheet1!A1quot; etc. But this does not also return the comments.
gt;
gt; I want references to return both the values and the comments (as
gt; comments).
gt;
gt; My real spreadsheet is a larger one, hence why I want this to be
gt; automatic.
gt;
gt; I am a VB novice although follow the basics so I could follow some
gt; clear guidance - I think!
gt;
gt; Thanks
gt;