# Need Help....

N

#### Naeem Mahmood

Is there a way to execute the result of =concatenate formula.

I have following formula =(CONCATENATE(B2,B15,B11,B3,B4))

That results in cell b19 as ... VLOOKUP(\$h\$10,'[Client Outstanding Status-2012.xls]Salvage Outstanding'!C1056:\$P\$3000,2,FALSE)

I would like to execute this in cell b20 as a vlookup formula.

Is there a way to do it without going into VBA?

Thanks
Naeem

J

#### James Ravenswood

No but it is possible with a very simple UDF:

Function eval(s As String) As Variant
eval = Evaluate(s)
End Function

For example, if A1 contains:
SUM
and A2 contains:
(B1:B2)
and A3 contains:
=CONCATENATE(A1,A2)
A3 displays the text string SUM(B1:B2)

In another cell, enter:
=eval(A3)
this cell will display the result of the SUM function.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=eval(A3)

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs

N

#### Naeem Mahmood

Hi James,

Somehow its not working for the formula I have...it is working if i do the sum(b1:b2)...

my formula is
=CONCATENATE("VLOOKUP(\$h\$10,'G:\Misc\Accounts Receivable\AR Tracker & Reminder\[Client Outstanding Status-2012.xls]Salvage Outstanding'!","C",C14,":\$P\$3000",",2,","False)")

which comes as
VLOOKUP(\$H\$10,'G:\Misc\Accounts Receivable\AR Tracker & Reminder\[Client Outstanding Status-2012.xls]Salvage Outstanding'!C1056:\$P\$3000,2,FALSE)

when i use the =EVAL(B19) the results is #REF

If i copy paste the result as value and add'=' i get the right result.

Not sure but I may be missing something.

Naeem

N

#### Naeem Mahmood

Its working....I just have to keep the other file open.