Show formula

[Randor]

Hi!



I am a student and I would like to show to my teacher the formulas I use in
my Excel worksheet.

Suppose I have this number in cell A1 :

5

and this number in cell A2 :

4

and this formula in cell A3:

=A1+A2

So, I will this result in cell A3:

9

In cell A4, I would like to see:

=5+4

Is there a formula ( for example Showformula(A3) ) I could place in cell A4
to see =5+4 in cell A4 ?




Thanks a lot!

 

[Sandy Mann]

Start the *formula* that you want to show with an apostrophe which will make
it text or pre-format the cell as text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

Sandy's answer assumes that you'll be typing exactly what you want to see in A4.

Just to add--there's nothing built into excel that will do this for you.
Depending on how complex the formula is, you may be able to get a UDF to parse
the formula string and evaluate each of the components.

But a general UDF (User defined function) would be pretty complex.

 

[Ken Johnson]

Hi!

I am a student and I would like to show to my teacher the formulas I use in
my Excel worksheet.

Suppose I have this number in cell A1 :

5

and this number in cell A2 :

4

and this formula in cell A3:

=A1+A2

So, I will this result in cell A3:

9

In cell A4, I would like to see:

=5+4

Is there a formula ( for example Showformula(A3) ) I could place in cell A4
to see =5+4 in cell A4 ?

Thanks a lot!

If you are limiting your formula to +,-,x or / then you could get away
with...

= "=" &A1&IF(A3=A1+A2,"+",IF(A3=A1-A2,"-",IF(A3=A1*A2,"x","/"))) &A2

Ken Johnson

 

[Dave Peterson]

Nice idea, but it would fail for expressions that would look like these:

=1*1
=1/1

=2*2
=2+2

But maybe it'll be enough for the OP???

 

[Charles W Davis]

Randor,

If you happen to be on Windows Vista, use the Snipping tool. Take an image
of the Formula bar similar to this:
http://www.anthemwebs.com/images/excelFormulabar.jpg

 

[RAGdyer]

You could use the XL 4.0 macro "Get.Cell".

You can make a *relative* named formula, which will display the contents of
a cell, whether data *OR* formulas!

By relative, I mean enter the formula in a cell, and display the contents of
the cell in a relative location to the referenced cell.

This relativity is established by the cell you select when you start, in
relation to the cell referenced in the 4.0 macro during creation.

Say your formulas will be in Column C, and you want to display them 2
columns over,. say in Column E.

To start, click in E1.
Then, from the menu bar:
<Insert> <Name> <Define>,

In the "Names In Workbook" box, type something like
formula
Then, *change* whatever's in the "Refers To" box to:
=GET.CELL(6,C1)
Then <OK>.

You've now created a relative named formula, which will display the cell
contents of the cell 2 columns to the left, no matter in which cell the
formula is entered.

So, if C1 contains a formula, like
=A1+B1
Entering
=formula
in E1 will display the formula, *not* the results of the formula!

Entering
=formula
in J10, will display the contents of H10, either value or formula.

If you wish, you could assign a cell to contain the cell address of the cell
that you wish to show the contents of.
Say you want to enter the cell to display in F1.

Once again, from the menu bar:
Insert> <Name> <Define>,

In the "Names In Workbook" box, type something like
form
Then, *change* whatever's in the "Refers To" box to:
=GET.CELL(6,Indirect($F$1))
Then <OK>.

So, in F1 enter
C1
That way, you could enter
=form
in *any* cell, and return the contents of C1.

 

[RAGdyer]

Forgot to add this CAVEAT:

Caveat:
This can safely be used in versions of XL, from XL02 onward.


Earlier versions *WILL* CRASH*, causing the loss of all unsaved work, when
attempting to copy these formulas to *other* WBs.


Can be used safely in earlier versions as long as copying is restricted to
sheets within the existing WB.

 

[Randor]

Hi Sandy!

But I understand correctly your answer, I will see this in celle A4:

=A1+A2

But, I would like to see

=5+4

Thanks!

 

[David McRitchie]

Possible quibble, but it doesn't really look to me like you would be showing
the formulas, but instead a bunch of constants that don't show how
they relate to anything. I think I would rather see the actual formulas.
See GetFormula and GetFormulaD in
http://www.mvps.org/dmcritchie/excel/formula.htm#getformula

The use in cell A4 would be coded, something like either of these
=GetFormulaD(A3)
=personal.xls!GetFormulaD(A3)
would appear as
A3: =A1+A2

So you could create a little a sample display somewhere
on the sheet as
=GetFormula(A1) =A1
=GetFormula(A2) =A2
=GetFormula(A3) =A3

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

 

[Randor]

Hi David!

It is very interesting.

But, I would like to really see the bunch of constants.
It would mean something to my teacher.

Thanks a lot!

 

[David McRitchie]

Hi Randor,
Really meant in the example to show =FormulaD(A3) =A3
so that it is shown where the formula is, but as you say it's not for you.

To do what you want, to place values for cell addresses in the last formula, you
would have to extend the macro to replace any Excel cell represented by
it's current value, in other words you would need to be able to parse
the string to identify cell addresses and replace them. I don't know
if you would be able to do that with Regular Expressions in the macro
o not (I sure would not be able to) and you would have to not change
constants within quotes.
="A1-" & RIGHT("00"&B1,2) & "-" & C1
=((D1*E1)+(F1/G1)*4)

Assuming that all of the values on row 1 are numbers
that you would want to see something like:
="A1-" & "002" & "-" & 3
=((4*5) +(6/7)*4) or would that just show one number

MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm