Display a Formula

[ASPENCO]

I would like to display a formula instead of a value of one cell i
another cell (i.e. I want to display the formula of cell a1 in a2. a
displays the value). Thanks

 

[Debra Dalgleish]

You could show the formula in an adjacent cell, by creating a
User Defined Function (UDF).

1. Press Alt+F11 to open the Visual Basic Editor.
2. In the Project Explorer, at the left, select your workbook.
3. Choose Insert>Module.
4. Paste in the following code:

Function ShowFormula(Rng As Range)
' show Formula from cell
ShowFormula = "=" & Right(Rng.formula, Len(Rng.formula) - 1)
End Function

5. Close the VBE.
6. In the worksheet, select cell A2
7. Type =ShowAllInfo(A1)
8. Press Enter.

 

[Gord Dibben]

Correction to Debra's instructions

7. should be Type =ShowFormula(A1)

One other version of ShowFormula

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function

Gord Dibben XL2002

 

[Debra Dalgleish]

Thanks Gord. (Never change formulas in midstream!)

 

[David McRitchie]

My preference is to show what was entered in a cell either a formula or a constant
so I prefer GetFormula as featured in
Show FORMULA of another cell in Excel
http://www.mvps.org/dmcritchie/excel/formula.htm

Variations to show off array formulas and text constants, and to display cell address
and formula and remarkably ShowFormula almost matches the code in this thread
which suppresses non formula.

Also on the above page are functions and macros to display conditional formula,
number format, an indicator if formula, font information, and a description of the Status Bar.

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