Concatenating Text to Formula

[maria90]

Hello everyone.

I would like to concatenate two cells to one formula - this will be in a table/listobject.
What the formula does it retrieves the count value from the column 'name' of different tables.

I am not allowed to use VBA for this project.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D (formula used)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]tblOrders1[/TD]
[TD][[#Totals];[Name]][/TD]
[TD]=tblOrders1[[#Totals];[Name]][/TD]
[TD]="="&CONCATENATE(A1;B1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]tblOrders2[/TD]
[TD][[#Totals];[Name]][/TD]
[TD]=tblOrders2[[#Totals];[Name]][/TD]
[TD]="="&CONCATENATE(A2;B2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]tblCustomers1[/TD]
[TD][[#Totals];[Name]][/TD]
[TD]=tblCustomers1[[#Totals];[Name]][/TD]
[TD]="="&CONCATENATE(A3;B3)[/TD]
[/TR]
</tbody>[/TABLE]

The concatenation works (column C) but I cannot convert text it to a formula. Some people have created a formula by adding a '=' in front of the concatenation, but that doesn't work for me.

Is there a way to create a formula from text which is in column C?

Thanks for your help.

 

[ImranMalek]

Hi,
There are couple of ways to do this
Easiest would be to replace '=' by '=' in column C which turn your text in column C to formula.

The other way would be to use INDIRECT

=INDIRECT(CONCATENATE(A1;B1))


You do not want VBA solution but just for the reference

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

You can call this function in cell D1 =Eval(C1)

Hope it helps.

 

[maria90]

Excellent - thanks for your help.

INDIRECT did the trick.

Maria