A function to display a formula that's defined within another cell ...

[Clive Green]

I need to discover whether there is a function which will retrieve the
FORMULA EXPRESSION that's held within another cell - for example, I'd
like to be able to write this expression in a cell:

=GETFORMULA(E4) //this would display the formula that's defined in cell
E4 as a string

Can anyone advise me on this? It would sure save me a heap of time!


- Clive Green

 

[JE McGimpsey]

I need to discover whether there is a function which will retrieve the
FORMULA EXPRESSION that's held within another cell - for example, I'd
like to be able to write this expression in a cell:

=GETFORMULA(E4) //this would display the formula that's defined in cell
E4 as a string

Can anyone advise me on this? It would sure save me a heap of time!

You can do this most easily with a User Defined Function (UDF). If
you're not familiar with UDFs, you may want to see David McRitchie's

Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Put this in a regular code module in your workbook. Type Opt-F11 to
enter the VBE. Choose Insert/Module, and in the window that opens, paste
in

Public Function GetFormula(ByVal rng As Range) As String
GetFormula = rng(1).Formula
End Function

 

[Fredrik Wahlgren]

I need to discover whether there is a function which will retrieve the
FORMULA EXPRESSION that's held within another cell - for example, I'd
like to be able to write this expression in a cell:

=GETFORMULA(E4) //this would display the formula that's defined in cell
E4 as a string

Can anyone advise me on this? It would sure save me a heap of time!


- Clive Green

Tools|Macro|Visual Basic Editor
Insert a new module i.e. Insert|Module

Paste this code into the module

Public Function ShowFormula(r As Range) As String
ShowFormula = r.FormulaLocal
End Function

You can now insert
=ShowFormula(E4) in any cell (except E4) and you will get the formula in
this cell

Regards,
Fredrik