Display Formula in A1, Result in A2

**ryan1234** · 09-26-2007, 12:34 PM

I have a formula in A1 that I would like to execute from A2. I want the A1 to display the formula (so no "="), but I would like cell A2 to execute the formula in A1.

If A1 contains: "SUM(1+2)", how can I get A2 to display "3" without reproducing the formula?

A1: SUM(1+2)
A2: ????

I suppose I'm looking for something like this:
A2: ==A1
But, of course, that doesn't work.

If I do this it gets close:
A2: ="="&A1
But that returns "=SUM(1+2)" instead of executing the formula.

What do you think?

**NBVC** · 09-26-2007, 12:49 PM

Try this:

Go to Insert|Name|Define and enter the word "FormEval" without quotes, in the Names in workbook field

In the refers to field enter: =EVALUATE(INDEX(A:A,ROW()-1))

Click Ok.

Now in A2, just enter FormEval

This will evaluate the formula in the cell directly above where you enter it.

**Ron Coderre** · 09-26-2007, 12:56 PM

Excel doesn't have an EVALUATE type of function.

You'd need to create a User Defined Function in VBA to effect what you want.

Here's an example, in its simplest form:

Please Login or Register  to view this content.

Usage:
If A1:A5 contains the series 1,2,3,4,5

B1: SUM(A1:A5)

This formula would return the results of the B1 expression:
C1: =EvalCell(B1)

In the example, it returns: 15
the sum of 1+2+3+4+5

Does that help?

**Ron Coderre** · 09-26-2007, 01:04 PM

I completely forgot about that little named_formula trick, NBVC!
If it's what the OP needs...then "Kudos" to you for thinking of it.

(They say the first thing to go is your eyes...and the second is...ummmm..I can't remember.)

**ryan1234** · 09-26-2007, 01:28 PM

Thanks for your quick reply. Unfortunately the VBA solution won't work for me — I'm running Excel 2004 on a Mac.

Is there anyway to do this without VBA?

**NBVC** · 09-26-2007, 01:31 PM

Not sure if my solution works on a Mac...but you could give it a try...it is not VBA

**ryan1234** · 09-27-2007, 12:13 AM

Unfortunately that didn't work for me either.

Anyone else?

I'm looking for this result...

---------------
| SUM(1+2) |
---------------
| 3 |
---------------

How can I display the result of the equation in A1 in A2?

**NBVC** · 09-27-2007, 07:50 AM

See attached for an example... does it work for you? You should see a 3 in A2 and in C2

Go to Insert|Name|Define and select FormEval to see formula....

**ryan1234** · 09-29-2007, 12:22 PM

It worked... And I understand now. Thanks for your patience and for the sample file.

One more challenge for you...

Is it possible for FormEval to include a variable from the cell it's called from?

Please Login or Register  to view this content.

Where FormEval is =EVALUATE(EVALUATE(A1 & VAR))
And the result in A2 is "Display this text in A2"

Another way to ask this is:
Is it possible to build a formula using 2 cells and evaluate that formula in a third cell?

See attachment for xls version of the above.

**NBVC** · 10-01-2007, 07:59 AM

You didn't leave an attachment.... so I guessed....see my attached.

In Sheet2, I have "Sheet1!" in A1 and "B2" in B1 and in Sheet1, I have "diplay this text in A2"

In Sheet2, A2, I enter formula =FormEval which is defined as =EVALUATE(Sheet2!A1&Sheet2!B1)

Hope this is what you needed. Let me know if it's not.