Display excel formula on worksheet

[superchica]

How do I display only one formula on an Excell worksheet without showing all
of the formulas on my worksheet?

 

[Ron de Bruin]

Hi superchica

You can use 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

And this in a worksheet cell for the formula that is in A1
=GETFORMULA(A1)

 

[David McRitchie]

To just see it, look at the formula bar with the cell selected.

If you will be printing it see
http://www.mvps.org/dmcritchie/excel/formula.htm#getformula
installation instructions in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
usage on the worksheet, depending on where you installed, any of
=GetFormula(G3)
=personal.xls!GetFormula(G3)
If it is not obvious where the formula actually is you might instead use
=personal.xls!GetFormulaD(G3)

Look over the rest of the web page, there are formulas to obtain
the formatting in another cell, and a heck of a lot more.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm