Convert text to formula

[willheap]

Hello Chandoo


Your blogs have solved many of my excel challenges over the last few years but I have come up against my first challenge that I can't find an answer to here. How do you convert a formula written as text into a working formula? e.g.


I have a report producing hundreds of variations of the following text in a column.

Cost = 71359.413 * X ^ 0.286

Cost = 111.586 + 0.843 * X

X is a variable which will be located in an adjacent cell to each formula.


Extracting the useful bits of formula and substituting X is the easy bit with various text formula but I can't get Excel to recognise this result as a formula


The formulae in the column are all different but do not get any more complicated than those above.


And by the way, absolutely no Macros are allowed in the solution, only formulae!


I'm looking forward to hearing back from you!


Cheers,


Will

 

[Hui]

You can ue the evaluate function to do this

Have a look here: http://chandoo.org/wp/2011/05/16/lost-excel-functions/

 

[xld]

Be aware that this will not cause an automatic recalculation, but it can be forced by adding something like &TEXT(TODAY(),"") to you formula.

 

[willheap]

Thanks Hui! That almost solved it The EVALUATE() function creates its own macro and I have to save the file in macro enabled format. Although this works for me, it will cause security issues for others which I need to avoid if possible :O


Are there any other options?