Function to return formula

E

Edward

What is the best way to return a cell's formula (using a function)?

I'm thinking along the lines of this (fictional) function:

=CELL("formula",A1)

TIA,

Ed
 
C

Chip Pearson

You can''t do it with a formula. You need to use a VBA function. E.g,

Public Function GetFormula(Rng As Range, _
Optional GetValueIfNoFormula As Boolean = True) As Variant

If Rng.Cells.Count > 1 Then
GetFormula = CVErr(xlErrRef)
Exit Function
End If
If Rng.HasFormula = True Then
GetFormula = Rng.Formula
Else
If GetValueIfNoFormula = True Then
GetFormula = Rng.Value
Else
GetFormula = vbNullString
End If
End If

End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)
 
R

RagDyeR

Caveat -

This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to within
the existing WB.

Start my creating a 'named' formula that will return the actual formula from
A1;


Click in B1, then,
<Insert> <Name> <Define>,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK>


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.

So, with A1 containing:

=A2+A3+A4

In B1 enter:

=form

To get the contents of A1.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



What is the best way to return a cell's formula (using a function)?

I'm thinking along the lines of this (fictional) function:

=CELL("formula",A1)

TIA,

Ed
 
E

Edward

I tried your suggestion, and it works. Thanks.

Where can I get more information about similar functions?

This is new to me.

Thanks again,

Ed
 
Top