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

Are you looking for your function to return the text of the formula, or just
have the same formula?

If you just want both cells to have the same formula, just put $ in front of
both parts of your cell reference.
so, if the formula is =A2, change the formula to =$A$2. then, no matter
where you put it, it will return the same value.

"Edward" wrote:

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

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)


"Edward" wrote in message
ups.com...
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

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!
================================================== ===



"Edward" wrote in message
ups.com...
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

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

RagDyeR wrote:
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!
================================================== ===



"Edward" wrote in message
ups.com...
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

These are called XL 4.0 macros.

Try this:

http://tinyurl.com/seb4r

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Edward" wrote in message
ups.com...
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

RagDyeR wrote:
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!
================================================== ===



"Edward" wrote in message
ups.com...
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