Excel - Calculate the function in a cell that is created with CONCATENATE

Asked By Matt on 20-Nov-09 05:18 PM
Background ....
$G$1 = a list
$M$6 = A7:K78
$N$6 = =CONCATENATE("'",L6,"'",M6)

I built a formula with the concatenate function:


That produces the desired formula:


But, that output function does not calculate unless I go into the cell and
F2 F9.

Is there a way to have Excel perform that function automatically?

Gary''s Student replied to Matt on 20-Nov-09 07:51 PM
N16 contains a string that looks like a formula.  Consider this very tiny
User Defined Function:

Function eval(r As Range) As Variant
eval = Evaluate(r.Value)
End Function

It calculates a string as a formula.  For example if A1 thru A4 contain:


and A5 contains:
=A4 & A3 & A2 & A1
A5 displays:

eval(A5) will display 3

Gary''s Student - gsnu200909
zvkmpw replied to Matt on 20-Nov-09 09:11 PM
There is a different approach that might be easier to use.

Instead of using CONCATENATE to build a formula, I approached it using

The purpose of OFFSET(...) here is to specify a table_array of the
needed height and width, placed properly in 'AG Q'.

For example, I tried
A1 contains the vertical offset in 'AG Q' where table_array starts
A2 contains the horizontal offset in 'AG Q' where table_array
A3 contains the height of table_array
A4 contains the width of table_array

Your case might not be as general as this, so modify to suit.