0

Been bashing my head over this for hours.

Here is the data and desired result:

A1 has the string 204+204+204+204+204+204+59

I need B1 to show the SUM of A1

I've managed thus far by just copying and pasting the text into B1 and placing a = in front of it i.e. (=204+204+... etc.) But I know there has got to be an easier way.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TJulander
  • 1
  • 1
    See this question: http://stackoverflow.com/questions/4471884/turn-a-string-formula-into-a-real-formula – David M Sep 08 '15 at 15:08

1 Answers1

2

Here is how to do it.

1

Open the Name Manager. Control-F3 from the worksheet, and then click the New button.

2

For the Name field in the dialog, enter EVALA. I just picked this name; it stands for "Evaluate A". But you can pick whatever name you like.

3

For the Refers to field, enter this

=EVALUATE($A1)

4

Click OK and then Close.

5

In B1 enter this formula:

=EVALA

That's it.

You can now use this formula on any row in the worksheet and it will evaluate whatever is in the column A cell of the row where you enter the formula.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Woah! Pretty clever. How come you can use `Evaluate` when creating a named range, but you can't use it as a regular formula? (meaning I can't just type `=Evaluate($a1)` into B1 and get the result) – BruceWayne Sep 08 '15 at 15:27
  • 1
    @BruceWayne Evaluate() hails from the macro language that Excel used prior to VBA. That was called Excel 4 Macro language. It still lingers in Excel to this day for backward compatibility. For example, besides worksheets and chart sheets, you can create macro sheets; these are for Excel 4 Macros. You cannot use Excel 4 Macros in cell formulas directly, but you can use some of the functions from Excel 4 Macros in the Name Manger. – Excel Hero Sep 08 '15 at 15:33
  • Is there some reason they are phasing out `Evaluate()` and the others? I don't think it was replaced by anything, so I'm curious if you know what the thinking might be. Is there some other function/method of doing things that's preferable over `Evaluate`, or did they just cut it because you gotta start cutting somewhere? – BruceWayne Sep 08 '15 at 15:38
  • 1
    It has not been cut. VBA is a much more powerful programming language than Excel 4 Macros ever was. But yet they kept the old macro language in there anyways. As far as using it directly from a cell, well you cannot do that with VBA either... but the analog to what I've done here with the Name Manager and the old macro language is using a UDF from VBA; that's how you can use VBA in a cell. Nothing has been cut. – Excel Hero Sep 08 '15 at 15:42
  • Now it would be nice if they chose to create a worksheet function EVALUATE() that would basically do the same thing. Not sure why they have never done so! – Excel Hero Sep 08 '15 at 15:47