Formula In Comment?

  • Thread starter Thread starter Vincent Wonnacott
  • Start date Start date
V

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
 
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)
 
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
 
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
 
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)
 
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)
 
Back
Top