How do display a formula
 

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

How do display a formula
 

the only way i know of is tools/formula auditing/evaluate formula

--


Gary


"Jonathan" <Jonathan @discussions.microsoft.com wrote in message
...
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

How do display a formula
 

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 dont 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.


--
Cheers
macropod
[MVP - Microsoft Word]


"Jonathan" <Jonathan @discussions.microsoft.com wrote in message ...
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

How do display a formula
 

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??


Gord Dibben MS Excel MVP

On Thu, 28 Aug 2008 13:44:07 +1000, "macropod"
wrote:

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.

How do display a formula
 

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


"Jonathan" <Jonathan @discussions.microsoft.com wrote in message
...
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

How do display a formula
 

Hi Gordon,

I noted the limitation as to what can be displayed in my reply.

--
Cheers
macropod
[MVP - Microsoft Word]


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
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??


Gord Dibben MS Excel MVP

On Thu, 28 Aug 2008 13:44:07 +1000, "macropod"
wrote:

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.

How do display a formula
 

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

On Thu, 28 Aug 2008 16:03:17 +1000, "macropod"
wrote:

Hi Gordon,

I noted the limitation as to what can be displayed in my reply.

How do display a formula
 

Hi Gord,

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

--
Cheers
macropod
[MVP - Microsoft Word]


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
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

On Thu, 28 Aug 2008 16:03:17 +1000, "macropod"
wrote:

Hi Gordon,

I noted the limitation as to what can be displayed in my reply.