Formula In Comment?

[Vincent Wonnacott]

Is it possible to enter a formula into the comment box, or is there anyway
of showing a value of another cell when hovering over a cell

 

[Frank Kabel]

Hi
AFAIK this is not possible

 

[Max]

Hi Frank,

Did re-collect a Dave Peterson's post in Jan 2002
which gave an event macro to work in a comment box
(To show sales figures converted from one currency to another)

http://tinyurl.com/32qxu

 

[Roger Whitehead]

You'll need vba programming (and by chance this has cropped up recently in
the EXCEL-L developers list). Do you want this available as new formulas are
added and/or for data in an existing worksheet?

Roger
Shaftesbury (UK)

 

[Vincent Wonnacott]

Roger

It is as part of a pivot table that is refreshed from a database source.

It represents sales by department, than I would like when hovering over a
department that to show a percentage as part of the total sales.

Vincent

 

[Roger Whitehead]

Why not add a custom pivottable field next to the department name?

R

 

[Vincent Wonnacott]

Didn't really want to do this because it is full of figures as it is and
looks a mass of figures, wanted these hidden until pointing to each of the
breakdown.

Vincent

 

[Roger Whitehead]

Then with thanks to Max/Dave P.....

Select the Departmental names (this assumes they're in a column in the
table). This also assumes the last cell is the Grand Total of Sales:

Sub LabelCells()
Dim GrandTotal As Long

lastcol =
ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

GrandTotal = Cells(lastrow, lastcol)

Dim cel As Range
Dim myval as variant
For Each cel In Selection
With cel
If .Comment Is Nothing Then
myval = Application.WorksheetFunction.Subtotal(9,
Range(cel.Offset(0, 1).Address & ":" & Cells(cel.Row, lastcol).Address))
myval = myval / GrandTotal
myval = Format(myval, "0.00%")
.AddComment myval
' .Comment.Visible = True
.Comment.Shape.TextFrame.AutoSize = True

End If

End With
Next cel
End Sub

Beware - comments dont seem to have teh same functionality in Pivot Tables
as in plain wsheets.


--
HTH
Roger
Shaftesbury (UK)

 

[Vincent Wonnacott]

Roger

Sorry for being dumb - where do I enter all of this?

 

[Roger Whitehead]

No, you're not being dumb, I've made unreasonable assumptions (again )

In your workbook-

ALT+F11 (or Tools, Macros, Visual Basic Editor)
Insert, Module
Paste the code (from the line beginning SUB to the END SUB line (inclusive))

My code makes some more assumptions about your pivot table, I hope these are
OK........ One to note is that the code expects your pivot table to be the
only thing on the sheet - or that anything else appears above it, or to the
left of it.

Select the department names - should be a group of contiguous cells, aligned
vertically (eg A6:A15)

Do ALT+F8 (or Tools, Macros)
Select the name of the Macro - probably the only one there - "LabelCells"
Click Run

Do this on a safe/disposable copy of your workbook, in case it all goes
amiss.


--
HTH
Roger
Shaftesbury (UK)