how to build a formula from a string of text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DR - 06 Mar 2008 20:17 GMT

I want to build a dynamic formula from values from different fields and be
ale to execute it as a formula. I'm able to get the value in a cell as
"=A1+A2" but not the result.
Any guss on how I can do that without using Copy + Past Special/Value and
then replace = sign .... function?

Reply to this Message

Conan Kelly - 06 Mar 2008 23:56 GMT

DR,

Use the indirect function:

=INDIRECT("A1+A2")

If you have "=A1+A2" as text in cell A3, then put this as your formula in
cell A4:

=INDIRECT(A3)

But when you use INDIRECT, I don't think it likes the first charcter of the
formula to be an equals sign. Look up INDIRECT() in XL's help to be sure.
If your formula string is "built" by other formulas, then do away with the
"=", or you could use this as your formula in A4

=INDIRECT(RIGHT(A3,LEN(A3)-1))

HTH,

Conan

>I want to build a dynamic formula from values from different fields and be
> ale to execute it as a formula. I'm able to get the value in a cell as
> "=A1+A2" but not the result.
> Any guss on how I can do that without using Copy + Past Special/Value and
> then replace = sign .... function?

Reply to this Message

Pete_UK - 07 Mar 2008 00:12 GMT

The trick is to set up a small user-defined function (often called
Eval) which makes use of the VBA Evaluate function - this does indeed
take a string which represents a formula and evaluates it. The
earliest reference I have found to such an approach was in 1999 - have
a look at this posting from Alan Linton:

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse
_frm/thread/96bf7c7b7bdaa7d5/0020e3b025605e68?lnk=st&q=alan+linton+eval#

Hope this helps.

Pete

On Mar 6, 11:56 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:

> DR,
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Reply to this Message