0

I'm working on a spreadsheet, and I'm trying to do something like this:

Price Returned      Net Profit

22                  22
45                  45
7                   7
25+25               50

The data is being put in as expressions, to denote the return values of specific items. What I can't figure out is how to make the next cell equal to the expression value of the first cell, so essentially as if the cell was "=22" and "=25+25". Any help would be greatly appreciated.

Vasu
  • 1,090
  • 3
  • 18
  • 35

2 Answers2

2

For simple things like that, you can make a UDF:

Function Evalu8(ByVal S As String) As String
    Evalu8 = Evaluate(S)
End Function

Called, like =Evalu8(A4)

Unfortunately, there's no built in worksheet function that'll do it for you, but the VBA Evaluate function saves the day.

Daniel
  • 12,982
  • 3
  • 36
  • 60
2

Your question triggered my memory of this SO question and @iDevlop's answer, which led to this post (which in turn has a dead link to Stephen Bullen's web page).

Basically, you can use the Evaluate function, which is actually an undocumented Microsoft XLM macro. You have to use it in a defined name, or you'll get a "The Function is Not Valid" error message. And since it's really a macro, the workbook it's in needs to be saved as a .xlm and macros have to be enabled for it to work. If all that works for you, here's how you'd do it (if it doesn't, have a look at the accepted answer to the SO question above, or @Daniel Cook's answer below which popped up while I was working on this answer):

  1. Select B1 in the worksheet with the calculation (Sheet1 in this example)
  2. Click Ctrl-F3 to open Excel's Name Manager
  3. Create a new name and call it EvaluateCellToLeft
  4. Set the scope to Sheet1
  5. In the refers to box, enter =evaluate(Sheet1!A1)

This is how it looks in Excel 2010:

enter image description here

Now in column B enter =EvaluateCellToLeft. Remember, it's a named range, not a formula, so there's no parentheses at the end.

enter image description here

Community
  • 1
  • 1
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Actually, I can't think of any reason to do all this rather than just add a macro like @Daniel Cook's. You've got the same macro-enabling requirements with this and less flexibility, i.e., with a name you have to specify the cell to be evaluated. And with his you could put it in an addin, avoiding the macro-enabling requirement, which I'm not sure you can with this. And who knows maybe someday Excel 4 macros will be discontinued. – Doug Glancy Dec 24 '12 at 03:27