Comments in cell (try #2)

[Guest]

Will someone offer some assistance with this pls,

Is it possible to have the result of this formula
=SUM(DynamicRange)/COUNTIF(DynamicRange,">0")displayed as a comment attached
to cell B1? The comment would need to continually update as dynamic data is
changing.

TIA

 

[PaulD]

: Will someone offer some assistance with this pls,
:
: Is it possible to have the result of this formula
: =SUM(DynamicRange)/COUNTIF(DynamicRange,">0")displayed as a comment
attached
: to cell B1? The comment would need to continually update as dynamic data
is
: changing.
:
: TIA
:
Here is some input

to add a comment to a cell use
Range("E1").AddComment.Text "Your Comment Here"
Note this assumes there is no comment already existing, if there is you will
get an error. Either check for an existing comment first or use On Error
Resume Next

To know when to add this comment, you would need to look for a change event
Private Sub Worksheet_Change(ByVal Target As Range)
'your add a comment to a cell code here
End Sub
You would need to check if the target range is in your dynamic range.

Post back if you get stuck
Paul D

 

[Tom Ogilvy]

What is dynamicrange supposed to be?

 

[Guest]

In this example it is the Name of a Dynamic Range I have set up for accepting
new data.

 

[Tom Ogilvy]

Right click on the Sheet tab of the worksheet that has the DynamicRange
defined. Select View Code. Paste in this code in the resulting module

Private Sub Worksheet_Calculate()
Me.Range("B1").NoteText ( _
Application.Sum(Range("DynamicRange")) / _
Application.CountIf(Range("DynamicRange"), ">0"))
End Sub