How to show the formula and the answer?
 

I would like to show the formula of a calculation in one cell and the answer
in another. Do I have to retype as text? Or, is it possible to show both
simultaneously with just one formula? Is this possible?

Example, in cell A6 type =sum(A1:A5) and have the formula show in cell A6
and the answer to the formua in cell B6.

How to show the formula and the answer?
 

you will have to retype the formula in the other cell, and change the format
of one of them to text. Formulas can only affect the cells they are written
in.

"Susan" wrote:

I would like to show the formula of a calculation in one cell and the answer
in another. Do I have to retype as text? Or, is it possible to show both
simultaneously with just one formula? Is this possible?

Example, in cell A6 type =sum(A1:A5) and have the formula show in cell A6
and the answer to the formua in cell B6.

How to show the formula and the answer?
 

Hi Susan

You can use a function if you want
Copy this function in a normal module


Function GETFORMULA(cell As Range) As String
'=GETFORMULA(A1) in a sheet for example
'Various but especially Dave Peterson and Dave McRitchie
Dim myFormula As String
Dim myAddress As String
GETFORMULA = ""
With cell.Cells(1)
'Grab Formulas first
If .HasFormula Then
'Note that we have R1C1 or A1 reference possibilities
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
myAddress = cell.Address(0, 0, xlA1)
Else
myFormula = .FormulaR1C1
myAddress = cell.Address(, , xlR1C1)
End If
'Insert prefix "{" and post fix "}" for array formulas
If cell.HasArray Then
GETFORMULA = myAddress & ": {=" & _
Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GETFORMULA = myAddress & ": " & myFormula
End If
Else
'Bog standard data entries
If Application.ReferenceStyle = xlA1 Then
myFormula = cell.Formula
myAddress = cell.Address(0, 0, xlA1)
Else
myFormula = cell.FormulaR1C1
myAddress = cell.Address(, , xlR1C1)
End If
GETFORMULA = myAddress & ": " & myFormula
End If
End With
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Susan" wrote in message ...
I would like to show the formula of a calculation in one cell and the answer
in another. Do I have to retype as text? Or, is it possible to show both
simultaneously with just one formula? Is this possible?

Example, in cell A6 type =sum(A1:A5) and have the formula show in cell A6
and the answer to the formua in cell B6.

this function was found on the excel guru site and works great:

Function showformula(rng As Range)
If rng.HasArray = True Then
showformula = "{" & rng.Formula & "}"
Else
showformula = rng.Formula
End If
End Function