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?

Thanks
Naeem

 

[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)

To learn more about macros in general, see:

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

or

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

 

[Naeem Mahmood]

Hi James,

Thanks for the reply.

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

 

[Naeem Mahmood]

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

Thanks James for your help.

Naeem