Text to Formula

[VK]

Hello,

I need to perform calculations using mutiple cell references on a long
spreadsheet. I am using a CONCATENATE function to generate the text of the
long formula. But, I am not able to get excel to think it a formula and not
as text.

I can always click on the cell, add "=" as the first character and hit
"return", it will show the value. But, if add "=" as part of the concatenate
function, excel doesnt think it to be formula, thus the text with the "=" is
displayed.

Any idea how to address this issue?

Thanks in advance

 

[Jacob Skaria]

Try the alternative &

=A1&B1&C1
for
=CONCATENATE(a1,b1,c1)

If this post helps click Yes

 

[Luke M]

YOu don't need to use CONCATENATE, as Jacob said. However, if you're trying
to build a formula, you need to place it inside the INDIRECT function.
Example:
=SUM(INDIRECT("A"&B1&":B"&B1))

Assuming cell B1 has the value of 3, this would give the sum of A3:B3.

 

[Dave Peterson]

Excel will never see the results of your formula as a formula without you doing
something special.

One way is to include that equal sign, convert the formula to a value
(edit|copy, edit|paste special|values and then converting those cells to real
formulas. (Maybe edit|Replace equal sign with equal sign.)

Another option is to use a user defined function.

Option Explicit
Function Eval(myStr As String) As Variant
Application.Volatile True
Eval = Application.Caller.Parent.Evaluate(myStr)
End Function


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=eval(a1)