Convert text to Formula in Excel 2007

sma.bakker

New Member
Joined
Feb 9, 2010
Messages
2
Hello Forum,

I need to build interactive formulas as part of a bigger table.

I have come only this far with the test model:

A1 1 (or any value)
B1 + (or any operator)
C1 2 (or any value)
D1 =1+2 as the result of ="="&A1&B1&C1 in text format

Now I want E1 to show the result. In this case a total of 3

How can I convert D1 to a formula in E1 that calculates the result and changes with any of the input values? I have spend ours on it without results :(. Can something this simple be that complex; PLEASE :confused:

Sebastiaan
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Welcome to the Board!

Maybe try in E1:

=eval(d1)

or:

=eval(A1&B1&C1)

Hope that helps.
 
Upvote 0
Pretty sure you need VBA to do that.
Here's a basic UDF to do it. May need tweaking for building complex formulas in this manner. But works for the basic example you provided.

Put this code in any standard module
Rich (BB code):
Public Function Eval(myval As String)
'Evaluates a text string as A formula
Eval = Evaluate(myval)
End Function

Then you can use this in a cell formula

=EVAL(A1&B1&C1)
 
Upvote 0
Ahhh. Thanks. I didn't think it was a native formula, but knew there was evaluate in vba, so figured it might be. But thanks for spotting that out.

So if you want to use my formula you will need to download the morefunc add-in.
 
Upvote 0
Depending on how complex your operands can be, you could use an if statement:

=IF(B1="+",A1+C1,IF(B1="-",A1-C1,IF(B1="/",A1/C1,A1*C1)))
 
Upvote 0
EVALUATE is available to Names.

(After selecting E1 per: proper relative addressing )
one could define a name Name: formulaResult RefersTo: =EVALUATE(Sheet1!$D1)

Putting =formulaResult in a cell (in row1) will then return the desired result.
 
Last edited:
Upvote 0
The =Eval or Evaluate function looked very promising and have tried the functions standalone and in a named cell. Sad to say, neither works. They do not appear to be standard dunctions. I'll see if I can download the function somewhere.

I will try to find and download "MoreFunc Add-In" as suggested. Hope that goes smoothly. Back to G:):)GLE..............

This is where I went:

http://www.freedownloadscenter.com/Business/MS_Office_Add-ins/Morefunc_Download.html

Download creates a setup file. Closed Excel, started setup, followed the instructions and SUPER. Re-started Excel and I have a whole series of new functions on the Formula ribbon. EVAL was there and worked. Problem solved.

Thank you all!

Sebastiaan

Sebastiaan
 
Upvote 0
EVALUATE will not work when in a formula that is entered in a cell.
EVALUATE will work when part of the definition of a Name.

e.g. Define a name
Name: Test
RefersTo = EVALUATE("2")

then,
putting =EVALUATE("2") in a cell will return an error
putting =Test in a cell will return 2.
 
Upvote 0
Hi evrybody,
Does enybody knows similiar formula that works with reference on closed workbook?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top