how to obtain the result of the formula in another cell

  • Thread starter Thread starter youngman
  • Start date Start date
Y

youngman

hi,
I use excel everyday ,but there is a problem that puzzled me for a long
time.when I input a formula ,for example,in one cell I input
(12.36+2.36)*25+36,and I want to obtain the result of the formula in
another cell,what should I do.I always get (12.36+2.36)*25+36 itself instead
of the result 404.maybe someone would tell me sth helpful,or would you give
some hints in vba.I think confirmedly it is unnecessary to input the formula
once again to get the result.though I havent solve the issue.
regards.
Thank you.
 
hi,sorry for not having my problem well explained ,

i dont In cell A1 enter the expression:
=(12.36+2.36)*25+36
instead
i just want toIn cell A1 enter the expression:
(12.36+2.36)*25+36(without ***=***)
(for i have to show the formula explicitly )
what should i do.
Click to expand...

Off the top of my head the only way I can see you being able to do
that is to use a User Defined Function like this one:

Function EvaluateMyExpression(ExpressionText) As Double

EvaluateMyExpression = Evaluate("=" & ExpressionText)

End Function

Copy that function and paste it into a standard module in your
workbook.

Then in cell B1 you could have the formula

=EvaluateMyExpression(A1)

That will return 404 in B1, even though the entry in A1 is just plain
text.
 
You can't do it as a worksheet function directly, but you can do it as a
defined name. For instance, you can insert a name into the workbook called
'this.value', and give it a reference of =EVALUATE(A1). If you have cell B1
selected when you create this name, for every cell that you input the
formula =this.value it will pick up and resolve the string in the previous
column.

This technique uses the XLM (Excel 4 & prior macro language) EVALUATE
function, which carries a health warning with it. There are many reports
that copying that cell and trying to paste it into a cell in another
worksheet will CRASH Excel (97 and possibly 2000), Unsaved changes would be
lost.

Personally, I don't get this in my version of XL2000, I get a message box
saying it can't be done, and as I use an EVAL UDF, it has never been a
problem for me, but be aware.


--

HTH

Bob Phillips

youngman said:
hi,sorry for not having my problem well explained ,

i dont In cell A1 enter the expression:
=(12.36+2.36)*25+36
instead
i just want toIn cell A1 enter the expression:
(12.36+2.36)*25+36(without ***=***)
(for i have to show the formula explicitly )
what should i do.

thank you.
Click to expand...
 
It would be much better to code the actual formula as a formula,
that way you know that you are working with a valid formula,
that does not have errors and that gets adjusted when you
insert/delete rows/columns. Then in your other cell use
a function to obtain the formula from the cell with the formula.
This way you are not prone to inevitable errors doing it the
other way around.

=GetFormula(D1)
=personal.xls!GetFormula(D1)

More information in
http://www.mvps.org/dmcritchie/excel/formula.htm

David McRitchie, Microsoft MVP, Excel
 
Back
Top