Formula shows as text in cell entered and result shows in another cell

I want to automate a worksheet so that I can enter a simple formula in a cell and have it show the formula as I entered it but also show the results in an adjacent cell.

For example:
In cell E10 I enter something like 16*(3*3), or 16*9, or =16*9 and cell H10 displays "144" (the answer to the equation entered in E10).

I have attached an Excel 2007 "ExcelTest.xlsx" worksheet showing some of my failed attempts.

I use data validation in column "B" to choose appropriate formula style (ohm's law), this in turn auto-fills appropriate labels for unknown variable in column "D" & "G" (E, I, R, P) & result in column "I" (V, A, Ohms, W) as well as conditionally formats results box in column "H" with color to indicate result category (V=purple, A=blue, Ohms=green, W=yellow). It is all working except I can't get the results box in column "H" to display result from formula entered in column "E".

I prefer to use formula as I haven't done VBA and it is still a mystery for me and I am short on time to learn it at the moment.

Ok, awesome mehmetcik! It works on the test file but I am still cloud as to why and how to reproduce your results.

I tried double-clicking on cell in column "H" to see its contents & associated cells, after moving away from cell it displayed "#NAME" instead of desired result even though it still contained name "=result" it no longer worked and I can't seem to make it work again unless I reopen the worksheet - why?

I then located the Name Manager (of which I am previously unfamiliar) and copied the function "=EVALUATE(Sheet1!$E5)" to a cell. When I pressed ENTER I got the error "That function is not valid." If I escape out of this it only displays the text "=EVALUATE(Sheet1!$E9)"

The function EVALUATE does not seem to exist in the function list, how would one find it and know what it does? Is there another list of functions just for the Name Manager?

Thank you so far and I appreciate any more light you could shed on this.

I work on multiple computers with differing vintages of software so it varies from time to time. I am currently using office 2007.

When I tried replicating your work on my spreadsheet and type "=result()" in the results cell I get "That function is not valid". However when I looked at the sample worksheet it had "=result" without the "()" so I tried that and it just copies the contents of the equation cell into the results cell - same as if I typed "=E5" or "=(E5)". I must be missing something...

Thanks mehmetcik,
I've been sleuthing on my own and found a fairly good explanation of how EVALUATE works (and doesn't work - outside of a Named Range) at:
http://www.myonlinetraininghub.com/e...luate-function

It said this was an old macro tool that was still embedded in Excel 2010 and stated the workbook would need to be saved as a macro-enabled workbook or .xlsm. I tried this and it still did not work. I wonder if my "Home & Student" edition of MS2007 does not support this function.

@FlameRetired

I tried this multiple times (and multiple ways) w/o success. I did however run across a simple UDF code at:

http://superuser.com/questions/25335...were-a-formula

that looks like this:

Function ev(r As Range) As Variant
ev = Evaluate(r.Value)
End Function

I entered this as a UDF using "Alt-F11", changed "ev" to "EVALUATE" and saved it as a project. This function now shows up in function list (but of course it doesn't work in a cell, only if used from Name Manager which doesn't allow you choose from the function list...very handy MS).

It works as everyone described now, yea! But it seems like there should have been an easier way than spending my entire afternoon & evening learning a whole new tool I will rarely use.

If anyone knows an easier way I'd love to hear it.

Thank you all for your kind advice and enough clues to help me figure it out. Why it didn't work outright as shown (as well as several places I found online) I don't know. I see no more suggestions for this issue after 24hrs so I will mark it as solved. Hopefully there are enough clues here to help the next person trying to do this.