Converting STRINGS into FORMULAS

[Dario Jardim Junior]

really needing this...

let's say A1 = "SUM"
B1 = "("
C1 = ")"
D1 = "A3:C8"

How can I put everything together and get the FORMULA =SUM(A3:C8) and its
result INSTEAD of the STRING "=SUM(A3:C8)"?
it's drivinhg me mad....I've tried CONCATENATE, &,... and all I get is the
string...

Thanks a lot


Dario - Brazil

 

[Don Guillett]

Why?
Would this satisfy your need?
=SUM(INDIRECT(D4))

 

[Tom Ogilvy]

You can't do this with the built in functions in Excel. Laurent Longre has
an addin that has an Eval function that will do it, or you could use macros
to do it.


http://longre.free.fr/english/index.html
download Morefunc

 

[Norman Harker]

'fraid not Don!

INDIRECT requires a cell reference:

<Help>
Ref_text is a reference to a cell that contains an A1-style
reference, an R1C1-style reference, a name defined as a reference, or
a reference to a cell as a text string. If ref_text is not a valid
cell reference, INDIRECT returns the #REF! error value.

=INDIRECT("="&A1&B1&D1&C1)
Returns #REF!

=("="&A1&B1&D1&C1)
Returns: =SUM(A3:C8) as text

But I can't get that to evaluate automatically.

I suppose that could use VBA to replicate a manual approach
Copy > Paste Special > Values > OK and then edit out the ' and Enter.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Stephen Bullen]

Hi Dario,

really needing this...

let's say A1 = "SUM"
B1 = "("
C1 = ")"
D1 = "A3:C8"

How can I put everything together and get the FORMULA =SUM(A3:C8) and its
result INSTEAD of the STRING "=SUM(A3:C8)"?
it's drivinhg me mad....I've tried CONCATENATE, &,... and all I get is the
string...

If you have the text for a function in a cell, you can use a defined name to
calculate its value in an adjacent cell. For example, select cell B1, click
on Insert > Name > Define and fill it out as:

Name: EvalCellToLeft
Refers To: =EVALUATE(A1)+RAND()*0

Now if you have the text "=SUM(A3:C8)" in a cell (e.g. D10) and type
=EvalCellToLeft in the cell to its right (e.g. E10), that cell will show the
result of the formula. The +RAND()*0 in the definition makes the name
volatile, ensuring it's always calculated as the source data changes.
Without that, it would only recalc using Ctrl+Alt+F9.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Don Guillett]

In this case d4 had the text a3:c8 so
=SUM(INDIRECT(D4))
summed the cells in that range

 

[Stephen Bullen]

Hi Don,

In this case d4 had the text a3:c8 so
=SUM(INDIRECT(D4))
summed the cells in that range

Sure - I was addressing the general case of evaluating a textual
formula, not the specific case in the OP's example. I was assuming
that he'd like to type, say, COUNT into A1 instead of SUM and then get
the count of the range.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Don Guillett]

I was responding to what Norman said...??

 

[Norman Harker]

Hi Don!

We're all probably right on this one. In principle you can't use
INDIRECT to build a textual formula but in this case it was possible
to feed the range in the way you did.

You popping round for some turkey this year?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Tom Ogilvy]

It might be useful to note that using an Excel 4 macro command (such as
evaluate) in this manner will cause problems if you copy the cell with the
=EvalCelltoLeft
formula in it to another worksheet (in versions earlier than xl2002).

You will get a message something like Unable to copy Macro command, then
when you click OK on that message, you will get a general protection fault
and your workbook will close losing any unsaved data.

As long as your are aware of that, it shouldn't be a problem.

 

[Don Guillett]

I think, like in the movie "Cool Hand Luke" , what we had was "a falure to
cumacat" (sp).
Always ready to have a bit of the bird. Merry Xmas and Happy Summer to you
people in the warm lands.

BTW, I saw a thing about the making of the movie "Lord of the Rings" showing
parts of New Zealand. Looks like a beautiful place. Do you go there?