Formula as text

  • Thread starter Thread starter Geoff
  • Start date Start date
G

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
 
I suppose I could create a custom function for this but I want to avoid this
if it can be done through Excel functions.
 
I think you'll need VBA.
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
Click to expand...
 
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.
 
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
 
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).
Click to expand...
....

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.
 
Back
Top