=vlookup(A1,Indirect("'[Text(Today(),"mmm_")&B1&".xls]Sheet1'!A1:B200"),2,Fa
lse)

Where B1 contains the company name.

[Jan_ABCCORP.xls]Sheet1!A1:B200

might be an example reference.

Jan_ABCCORP.xls must be open for this to work.

--
Regards,
Tom Ogilvy


shaharul wrote in message
...
can somebody help me with creating a variable lookup formula in VBA with
the table array referring to various workbooks depending on the months
and company to be extracted from. i have try to work it out but all
sort of errors come out.

putting ".Value" seems a good idea but i got a message for 'run-time
error '1004''. anybody care to explain to me what is that error and how
to solve it in simple english.
thank you very much.

as a reference, this approach works fine:

workbook_Name = "Ufrm_modeless_test.xls"
worksheet_Name = "Sheet1"
range_Area = "A1:B20"
?
workbooks(workbook_name).Worksheets(worksheet_name ).Range(range_Area).Addres
s
$A$1:$B$20
?
workbooks(workbook_name).Worksheets(worksheet_name ).Range(range_Area).Addres
s(external:=True)
[Ufrm_modeless_test.xls]Sheet1!$A$1:$B$20


If you put value on the end, this returns an array (if range_area is multi
cell). However, I believe if there is a problem with the assignment to a
variable, that would be type mismatch error.

--
Regards,
Tom Ogilvy



"shaharul" wrote in message
...
putting ".Value" seems a good idea but i got a message for 'run-time
error '1004''. anybody care to explain to me what is that error and how
to solve it in simple english.
thank you very much.