Indirect references in a linked formula

[Markshnier]

I have a formula in a workbook called my_spreadheet1.xls

=VLOOKUP(A1,'[my_spreadsheet2.xls]Main'!$1:$65536,27,FALSE)

i.e. In my_spreadsheet1.xls I have a formula that links to anohe
Workbook called my_spreadsheet2.xls.

My problem is that I want to have the name of the referenced Workboo
and the Worksheet within that Workbook be variable based on the value
in a couple of other cells.

For example if cell a2 = xxxxxxxxxx.xls, and cell a3 = yyyyyyy,
if would like to be able to create formula like

VLOOKUP(A4,'[xxxxxxxx.xls]yyyy'!$1:$65536,27,FALSE), where the xxxx
and yyyyy are some function that will insert the contents of thos
cells into the formula as text.

I have tried to use ADDRESS and INDIRECT where I have the xxxx an
yyyyy , but I can't figure ouit the syntax to make it work.

Any help would be appreciated.

Mar

 

[nh]

Assuming the location of the file is on cell D5 use the following formula
=INDIRECT("'"&D5&"Main'!$1:$65536") where D5 contains
'C:\Documents and Settings\xxx\My Documents\[my_spreadsheet2.xls]