MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Calculate the function in a cell that is created with CONCATENATE

• Follow

```Background ....
\$G\$1 = a list
\$L\$6 = =CONCATENATE(RIGHT(E1,2)," Q")
\$M\$6 = A7:K78
\$N\$6 = =CONCATENATE("'",L6,"'",M6)

I built a formula with the concatenate function:

=CONCATENATE("=VLOOKUP(G1,",N6,",2,FALSE)")

That produces the desired formula:

=VLOOKUP(G1,'AG Q'!A7:K78,2,FALSE)

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?
--
Matt
```
 0

```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:

1
+
2
=

and A5 contains:
=A4 & A3 & A2 & A1
A5 displays:
=2+1

eval(A5) will display 3

--
Gary''s Student - gsnu200909

"Matt" wrote:

> Background ....
> \$G\$1 = a list
> \$L\$6 = =CONCATENATE(RIGHT(E1,2)," Q")
> \$M\$6 = A7:K78
> \$N\$6 = =CONCATENATE("'",L6,"'",M6)
>
> I built a formula with the concatenate function:
>
> =CONCATENATE("=VLOOKUP(G1,",N6,",2,FALSE)")
>
> That produces the desired formula:
>
> =VLOOKUP(G1,'AG Q'!A7:K78,2,FALSE)
>
> 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?
> --
> Matt
```
 0

```> Background ....
> \$G\$1 = a list
> \$L\$6 = =CONCATENATE(RIGHT(E1,2)," Q")
> \$M\$6 = A7:K78
> \$N\$6 = =CONCATENATE("'",L6,"'",M6)
>
> I built a formula with the concatenate function:
>
> =CONCATENATE("=VLOOKUP(G1,",N6,",2,FALSE)")
>
> That produces the desired formula:
>
> =VLOOKUP(G1,'AG Q'!A7:K78,2,FALSE)
>
> But, that output function does not calculate unless I go into the cell and
> F2 F9.

There's a different approach that might be easier to use.

Instead of using CONCATENATE to build a formula, I approached it using
=VLOOKUP(G1,OFFSET(...),2,FALSE)

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
=VLOOKUP(G1,OFFSET('AG Q'!\$A\$1,A1,A2,A3,A4),2,FALSE)
where
A1 contains the vertical offset in 'AG Q' where table_array starts
A2 contains the horizontal offset in 'AG Q' where table_array
starts
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.
```
 0

2 Replies
334 Views

8/19/2012 3:38:40 PM