formula with text values

[Combo]

Hi,

In cell A1 I have "3*50+1*120+2*25", with och without ", that doesn't matter
in this case. The value in A1 is for display for the user, so that the user
can see what values being summarized into cell A2.

Now, for the question. How can a write a formula that makes a SUM in cell A2
that uses the values in cell A1? If I put a = in cell A1 Excel will make the
calculation but if I want that value to show in cell A2?

Best regards,

/Combo

 

[Mike H]

I don't know how to do that without the = sign in a1 but this may work. In A1
type
'=3*50+1*120+2*25
You won't see the '
select A1 and
Data|text to columns
Fixed Width
Next
Next
In the destination box type $A$2
Click finish

Mike

 

[dlw]

You question is confusing, in A2 you want to see a sum using A1 and other
cells AND the value of A1? You can't see both.

 

[Tom Hutchins]

If I understand your request correctly, you have a text representation of a
formula in A1, which may or may not include double quotes. In A2, you want to
see what this formula would yield if if was a real formula, not just text.
Try the following user-defined function (paste it into a VBA module in your
workbook):

Function RetVal(Target As Range) As Variant
Dim str As String
'Target must be a single cell
If Target.Count > 1 Then
RetVal = vbNullString
Exit Function
End If
'Remove double quotes, if any.
str$ = Replace(Target.Value, """", vbNullString)
'Evaluate the cell entry.
RetVal = Evaluate("=" & str$)
End Function

In A2, you would call this function like this:

=RetVal(A1)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch