Formula as text

[Geoff]

Without changing any of the options to reflect the formula in a worksheet,
though an Excel worksheet function is it possible to return the formula in
another cell.

E.g.

Cell A1=sum(a2:a200)
The value returned and represented in the worksheet is say 2000

I want to return the text "=sum(a2:a200)" through a formula and not VBA. Any
ideas or suggestions?

Thanks in advance

[Geoff]

I suppose I could create a custom function for this but I want to avoid this
if it can be done through Excel functions.

"Geoff" <gh@bob.com> wrote in message
news:OrXu7WeYFHA.4032@tk2msftngp13.phx.gbl...
> Without changing any of the options to reflect the formula in a worksheet,
> though an Excel worksheet function is it possible to return the formula in
> another cell.
>
> E.g.
>
> Cell A1=sum(a2:a200)
> The value returned and represented in the worksheet is say 2000
>
> I want to return the text "=sum(a2:a200)" through a formula and not VBA.
> Any ideas or suggestions?
>
> Thanks in advance
>

[Dave Peterson]

I think you'll need VBA.

Geoff wrote:
>
> Without changing any of the options to reflect the formula in a worksheet,
> though an Excel worksheet function is it possible to return the formula in
> another cell.
>
> E.g.
>
> Cell A1=sum(a2:a200)
> The value returned and represented in the worksheet is say 2000
>
> I want to return the text "=sum(a2:a200)" through a formula and not VBA. Any
> ideas or suggestions?
>
> Thanks in advance

--

Dave Peterson

[Earl Kiosterud]

Geoff,

As a related thing, you can show all formulas with Tools - Options - View
tab - Formulas. The shortcut to toggle it is Ctrl `. I don't know what
that thing's called, but it's under the tilde (~). Left of the 1 key.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Geoff" <gh@bob.com> wrote in message
news:OrXu7WeYFHA.4032@tk2msftngp13.phx.gbl...
> Without changing any of the options to reflect the formula in a worksheet,
> though an Excel worksheet function is it possible to return the formula in
> another cell.
>
> E.g.
>
> Cell A1=sum(a2:a200)
> The value returned and represented in the worksheet is say 2000
>
> I want to return the text "=sum(a2:a200)" through a formula and not VBA.
> Any ideas or suggestions?
>
> Thanks in advance
>

[Bob Umlas]

to display the formula in the cell to the left, for example, select cell F1
(arbitrary), then define a name, say Fml, and have it be:
=GET.CELL(6,Sheet1!E1) (where E1 is a relative reference to the cell to the
left of the active cell).
Then, if you enter =sum(A1:A5) in cell G12, for example, if you enter =Fml
in cell H12, you'll see =SUM(A1:A5) displayed.
Bob Umlas
Excel MVP


"Geoff" <gh@bob.com> wrote in message
news:OrXu7WeYFHA.4032@tk2msftngp13.phx.gbl...
> Without changing any of the options to reflect the formula in a worksheet,
> though an Excel worksheet function is it possible to return the formula in
> another cell.
>
> E.g.
>
> Cell A1=sum(a2:a200)
> The value returned and represented in the worksheet is say 2000
>
> I want to return the text "=sum(a2:a200)" through a formula and not VBA.
Any
> ideas or suggestions?
>
> Thanks in advance
>
>

[RagDyer]

Boy ... that takes me back a *lot* of years!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Umlas" <rumlas@kpmg.com> wrote in message
news:%23LxE0biYFHA.796@TK2MSFTNGP09.phx.gbl...
> to display the formula in the cell to the left, for example, select cell
F1
> (arbitrary), then define a name, say Fml, and have it be:
> =GET.CELL(6,Sheet1!E1) (where E1 is a relative reference to the cell to
the
> left of the active cell).
> Then, if you enter =sum(A1:A5) in cell G12, for example, if you enter =Fml
> in cell H12, you'll see =SUM(A1:A5) displayed.
> Bob Umlas
> Excel MVP
>
>
> "Geoff" <gh@bob.com> wrote in message
> news:OrXu7WeYFHA.4032@tk2msftngp13.phx.gbl...
> > Without changing any of the options to reflect the formula in a
worksheet,
> > though an Excel worksheet function is it possible to return the formula
in
> > another cell.
> >
> > E.g.
> >
> > Cell A1=sum(a2:a200)
> > The value returned and represented in the worksheet is say 2000
> >
> > I want to return the text "=sum(a2:a200)" through a formula and not VBA.
> Any
> > ideas or suggestions?
> >
> > Thanks in advance
> >
> >
>
>

[Geoff]

Thanks to all. I learnt a few new things here. Great!

"RagDyer" <RagDyer@cutoutmsn.com> wrote in message
news:e%23pjAwiYFHA.712@TK2MSFTNGP14.phx.gbl...
> Boy ... that takes me back a *lot* of years!<g>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Bob Umlas" <rumlas@kpmg.com> wrote in message
> news:%23LxE0biYFHA.796@TK2MSFTNGP09.phx.gbl...
>> to display the formula in the cell to the left, for example, select cell
> F1
>> (arbitrary), then define a name, say Fml, and have it be:
>> =GET.CELL(6,Sheet1!E1) (where E1 is a relative reference to the cell to
> the
>> left of the active cell).
>> Then, if you enter =sum(A1:A5) in cell G12, for example, if you enter
>> =Fml
>> in cell H12, you'll see =SUM(A1:A5) displayed.
>> Bob Umlas
>> Excel MVP
>>
>>
>> "Geoff" <gh@bob.com> wrote in message
>> news:OrXu7WeYFHA.4032@tk2msftngp13.phx.gbl...
>> > Without changing any of the options to reflect the formula in a
> worksheet,
>> > though an Excel worksheet function is it possible to return the formula
> in
>> > another cell.
>> >
>> > E.g.
>> >
>> > Cell A1=sum(a2:a200)
>> > The value returned and represented in the worksheet is say 2000
>> >
>> > I want to return the text "=sum(a2:a200)" through a formula and not
>> > VBA.
>> Any
>> > ideas or suggestions?
>> >
>> > Thanks in advance
>> >
>> >
>>
>>
>

[Harlan Grove]

Bob Umlas wrote...
>to display the formula in the cell to the left, for example, select cell F1
>(arbitrary), then define a name, say Fml, and have it be:
>=GET.CELL(6,Sheet1!E1) (where E1 is a relative reference to the cell to the
>left of the active cell).
....

Standard caveat: under Excel 2000 and prior, copying a cell containing
a defined name that includes an XLM function in its definition and
pasting into other worksheets will CRASH Excel with total session data
loss. Use XLM *VERY* carefully. Use udfs instead of XLM if macro
security isn't an issue.