CODEWITHSUNDEEP
excel
Vasu
Vasu

Reputation: 1110

How to make a cell equal to the value of an expression in another cell (Excel 2010)?

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.

Upvotes: 0

Views: 27064

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

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

Upvotes: 2

Daniel
Daniel

Reputation: 13122

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.

Upvotes: 2

Related Questions