How to show the formula and the answer?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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