How do display a formula

J

Jonathan

Hi,
Is it possible to show the actual numbers that were used for a computation?
i.e:

In cell A1 I have '2'
In cell A2 I have '3'

In cell A3 I have '=A1*A2' Therefore, Excel displays 6.

Is it possible to see '2*3' somehow??

Thanks,
Jonathan
 
M

macropod

Hi Jonathan,

One way is to go into Tools|Options|View & check the 'Formulas' option. Note that this causes Excel to display the formulae instead
of their results and, if your formulae are referencing other cells, its the references that display, not the contents of those other
cells.

An alternative is to use a macro that adds the formulae to the cell comments, then display the comments. Here's a macro to do just
that:
Sub AddFormulasToComments()
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
Set CommentRange = Range(Selection.Address)
End If
'If the cell contains a formula, turn it into a comment.
For Each TargetCell In CommentRange
With TargetCell
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
.Comment.Delete
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
End If
End With
Next
MsgBox " To print the comments, choose" & vbCrLf & " File|Page Setup|Sheet|Comments," & vbCrLf & "then choose the required print
option.", vbOKOnly
Application.ScreenUpdating = True
End Sub

By default, worksheet comments don’t print. To print the comments, choose File|Page Setup|Sheet|Comments, then choose the required
print option. The code provides a message to that effect.
 
G

Gord Dibben

OP wanted the actual numbers to show, not the formula.
In cell A1 I have '2'
In cell A2 I have '3'

In cell A3 I have '=A1*A2' Therefore, Excel displays 6.

Is it possible to see '2*3' somehow??
Click to expand...


Gord Dibben MS Excel MVP
 
R

Rick Rothstein \(MVP - VB\)

Here is the problem with what you are asking for as I see it... you are
thinking too small. For the formula you showed, fine, no problem, but what
about something like this?

=SUMPRODUCT((A1:A1000=B1:B1000)*C1:C1000)

There are 1000 values times three involved in those ranges... what, and how,
would you display all that? Just showing the content of the end of range
cells would convey absolutely nothing about the actual numbers involved in
calculating this formula. And trust me when I say this... that is a
relatively tame example... I could conceive of much, much more complex
examples.

Rick
 
G

Gord Dibben

I saw that.

Just wondered why the elaborate macro to add all those Comments that don't
give OP what he asked for in the first place.

To achieve the same thing as your macro without the Comments just enter
=ShowFormula(cellref) in an adjacent cell..............

Function ShowFormula(Cell)
Application.Volatile
ShowFormula = "No Formula"
If Cell.HasFormula Then ShowFormula = Cell.Formula
End Function

But still does not do what OP wantes.


Gord
 
M

macropod

Hi Gord,

I din't want to add anything to another cell in case that cell was or could be used for something else.
 
Top