Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

You might like to try this user-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

It takes a text string which represents an Excel formula (without the
equals sign) and evaluates it as if it were a formula. Use it like:

=eval(A1)

if A1 contains your string.

Hope this helps.

Pete


On Dec 20, 3:01 pm, lawson wrote:
Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)

If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

On Thu, 20 Dec 2007 07:01:00 -0800, lawson
wrote:

Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

A simple VBA UDF is needed:


============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
===============================
--ron

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?

"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:01:00 -0800, lawson
wrote:

Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

A simple VBA UDF is needed:


============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
===============================
--ron

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

fantastic, thank you very much. this tool should be a standard funtion of
Excel!

"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

is there a way to have the result show the value in the referencd cells
rather than the reference to them? ie:

rather than showing:
=ROUNDDOWN((D31-M83*2-I18*2-I16)/I19,0)+1

it would show:
=ROUNDDOWN((500-33*2-2.4*2-22)/666,0)+1


"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

On Thu, 20 Dec 2007 09:23:00 -0800, lawson
wrote:

is there a way to have the result show the value in the referencd cells
rather than the reference to them? ie:

rather than showing:
=ROUNDDOWN((D31-M83*2-I18*2-I16)/I19,0)+1

it would show:
=ROUNDDOWN((500-33*2-2.4*2-22)/666,0)+1



Yes, it can be done.

In the UDF, after extracting the formula, you would step through the formula
and, with anything that looks like a cell reference, substitute the
cell_ref.value (or cell_ref.text, if you want it formatted), for that
reference.

In addition to looking for cell references, you would also have to look for
Names that refer to values or cell references, and resolve them.

You would also have to take into account the differences between XL2003 and
2007.

Do you really need it?
--ron