Need Help....


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?




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:
and A2 contains:
and A3 contains:
A3 displays the text string SUM(B1:B2)

In another cell, enter:
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:


To learn more about macros in general, see:

for specifics on UDFs

Naeem Mahmood

Hi James,

Thanks for the reply.

Somehow its not working for the formula I 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 Mahmood

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

Thanks James for your help.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads