Need function to convert string to formula

[General Ledger]

Dear All,

Using Excel 2007: I used the Concatenate function to create a string that looks like a formula. I need a function that converts the string to a formula. I don't want to use VBA or macro.

For example, in cell A4 is the formula =CONCATENATE("=",A1,"!",A2,A3) Where A1 is the name of a sheet such as January, where A2 is a letter such as Z that is a column reference, and where A3 is a number such as 100 that is a row reference. The Concatenate function returns a string =January!Z100 I want Excel to interpret this string as a formula and show the value from sheet January, cell Z100.

It seems this should be easy but I can not find the solution. I almost wish the answer is complicated so I don't think I am so stupid. If someone provides a simple solution, I might have to surrender my Microsoft Certified Trainer and Certified Application Specialist certificates.

Best regards,

General Ledger

 

[Blade Hunter]

=indirect(Cell Where Your Concat goes in here)

Or in your case: =Indirect(CONCATENATE(A1,"!",A2,A3))

 

[General Ledger]

Blade Hunter,

@&#$*%! I have been thinking about this problem off and on for a long time and you solved it in seconds. My hat is off to you, my Master.

Thanks,

GL

 

[Blade Hunter]

Happy to help

Cheers

Dan

 

[mikerickson]

IF you have a more complicated formula you could use a Named Value

For example, if B3 held the STRING '=SUM(A1:A5)

Then the named formula
Name: calculatedValue
RefersTo: =EVALUATE(Sheet1!$B$3)

will return the value of the formula.

One drawback of this is that A1:A5 are not precedents of the formula, thus the formula will not refresh when they are changed.

A work-around for that would be to use
RefersTo: =IF(ROWS(Sheet1!$1:$65536)>0,EVALUATE(Sheet1!$B$3),)

where every cell of the sheet is a precedent cell for the named formula.

 

[gagan_blue]

Dear Mikerickson - This is magic sir. I was trying to implement something similar but nothing was working out. I wanted different formulas to execute based on certain criteria. I could build a very long choose formula but i wanted to keep it short by storing formulas as text string in cells. The method outlined by you made it possible.

 

[meytithveasna]

Mike,could you give a vba code UDF for ((200-100)*1.5) calculation as a result 150
if i have formula Sum((200-100)*1.5) i want to formulate it as ((200-100)*1.5)
could you help me?

 

[mikerickson]

150 is a constant value, it needs neither a formula nor VBA

 

[meytithveasna]

What formula?could you formulate?
note ((200-100)*1.5) is a text string

 

[mikerickson]

Function myEvaluate(aString) As Variant
    On Error Resume Next
        MyEvaluate = Evaluate(aString)
    On Error Goto 0
End Function